Updating MySQL ... tricky

Question

The Midnighter 0 Newbie Poster

15 Years Ago

I'm trying to create a form that updates a MySQL record when the user clicks a LINK, not a button.
I don't want to open another page for this. There really isn't a need for me to post my code right now, as I'm trying to figure out how to do this method properly.

Thanks in advance.

php

3 Contributors
8 Replies
125 Views
7 Hours Discussion Span
Latest Post 15 Years Ago Latest Post by Designer_101

All 8 Replies

JRSofty 18 Junior Poster in Training

15 Years Ago

Can we see the HTML you are using to build your form? This might clue us in on what exactly you are trying to do.

If you are trying to get a link on a form to act like a button then you can add a javascript event handler to it so that when the link is clicked the form is submitted.

JRSofty 18 Junior Poster in Training

15 Years Ago

Ok so your basic choices here are as follows:

1. Use javascript to add the values to the update link whenever they are edited (not an easy feat).
2. Use a proper form to post the information. A proper form for your input tags would have not only the method (post would be the best bet) but also the submit action (can be the same page). This way the information in the form gets passed as $_POST data. Then you can use it to update your database record.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

Designer_101 -2 Posting Whiz · Answer 1 · 2009-04-15T18:00:15+00:00

All you should do is this:

<a href="?update=true">Update</a>

And then test for that post variable to update the mysql:

if (isset ($_REQUEST['update'])) {
$query = "UPDATE table_name
SET column1=value, column2=value2,...
WHERE some_column=some_value";
if (mysql_query($query) {
echo "Update Done";
} else {
echo "Something went wrong";
}
}

// THE REST OF YOUR PAGES CONTENT

Hope this helps

The Midnighter 0 Newbie Poster · Answer 2 · 2009-04-15T18:25:01+00:00

That got me started, however now I'm curious..
How do I pass the values from the form, into the same page?

The Midnighter 0 Newbie Poster · Answer 3 · 2009-04-15T18:33:05+00:00

<?php
include('database.php');

$sup = "SELECT * FROM `suppliers` ORDER BY `supplier`";
$supResult = mysql_query($sup);
$manu = "SELECT * FROM `manufacturer` ORDER BY `Manufacturer`";
$manuResult = mysql_query($manu);

$id = $_GET['ID'];
$query = "SELECT tables2.ID,tables2.`INVOICE DATE`,manufacturer.Manufacturer,tables2.model,suppliers.supplier,tables2.invoice,tables2.MODEL,tables2.description,tables2.serial,tables2.price,tables2.`end user`,tables2.`customer price`,tables2.`MEI Invoice`,tables2.upc FROM `tables2` JOIN `suppliers` ON suppliers.ID = tables2.supplier JOIN `manufacturer` ON  manufacturer.ID = `tables2`.`manufacturer` WHERE `tables2`.`ID` = '".$ID."';"; 
$result = mysql_query($query);
$num = mysql_num_rows($result);
?>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">

<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title></title>
<link href="style.css" rel="stylesheet" type="text/css" />
<script type="text/javascript">
function openwindow(url){ NewWindow=window.open(url,'newWin','width=500,height=300,left=20,top=20,toolbar=No,location=No,scrollbars=no,status=No,resizable=no,fullscreen=No');  NewWindow.focus(); void(0); }
</script>
</head>

<body class="twoColElsLtHdr">
<div id="container">
  <div id="header">
    <h1>Maximum Electronics</h1>
  </div>
  <div id="sidebar1">
    <h3>Links</h3>
	<?php include('links.php') ?>
  </div>
  <div id="mainContent">
    <h1> Inventory Script</h1>
    <?php
	if ($num != 0) 
	{
	$i=0;
	
	while ($i < $num) {
		$f1=mysql_result($result,$i,"ID");
		$f2=mysql_result($result,$i,"INVOICE DATE");
		$f3=mysql_result($result,$i,"SUPPLIER"); 		if ($f3 == "Not Entered") { $f3 = "<strong>Not entered</strong>"; }  // Add bold
		$f4=mysql_result($result,$i,"INVOICE");
		$f5=mysql_result($result,$i,"MANUFACTURER");
		$f6=mysql_result($result,$i,"MODEL");
		$f7=mysql_result($result,$i,"DESCRIPTION");
		$f8=mysql_result($result,$i,"SERIAL");
		$f9=mysql_result($result,$i,"PRICE");
		$f10=mysql_result($result,$i,"END USER");
		$f11=mysql_result($result,$i,"CUSTOMER PRICE");
		$f12=mysql_result($result,$i,"MEI Invoice");
		$f13=mysql_result($result,$i,"UPC");
	?>
    
    <table border="1" cellpadding="2" cellspacing="2">
      <tr>
        <th>ID</th>
        <th><?php echo $f1; ?></th>
      </tr>
      <tr>
        <th>Invoice Date</th>
        <th><input name="invoiceDate" id="invoiceDate" type="text" value="<?php echo $f2; ?>" /></th>
      </tr>
      <tr>
        <th>Supplier</th>
        <th><SELECT name="supplier"><?php while($row = mysql_fetch_array($supResult)) { echo "<OPTION VALUE=".$row['supplier'].">".$row['supplier']."</OPTION>" ; } ?></SELECT></th>
      </tr>
      <tr>
        <th>Manufacturer</th>
        <th><SELECT name="manufacturer"><?php while($row = mysql_fetch_array($manuResult)) { echo "<OPTION VALUE=".$row['Manufacturer'].">".$row['Manufacturer']."</OPTION>" ; } ?></SELECT></th>
      </tr>
      <tr>
        <th>Invoice</th>
        <th><input name="invoice" id="invoice" type="text" value="<?php echo $f4; ?>" /></th>
      </tr>
      <tr>
        <th>Model</th>
       	<th><input name="model" id="model" type="text" value="<?php echo $f6; ?>" /></th>
      </tr>
      <tr>
        <th>Description</th>
        <th><input name="description" id="description" type="text" value="<?php echo $f7; ?>" /></th>
      </tr>
      <tr>
        <th>Serial</th>
        <th><input name="serial" id="serial" type="text" value="<?php echo $f8; ?>" /></th>
      </tr>
      <tr>
        <th>Price</th>
        <th><input name="price" id="price" type="text" value="<?php echo $f9; ?>" /></th>
      </tr>
      <tr>
        <th>End user</th>
        <th><input name="endUser" id="endUser" type="text" value="<?php echo $f10; ?>" /></th>
	  </tr>
      <tr>
        <th>Customer price</th>
        <th><input name="customerPrice" id="customerPrice" type="text" value="<?php echo $f11; ?>" /></th>
      </tr>
      <tr>
        <th>MEI Invoice</th>
        <th><input name="meiInvoice" id="meiInvoice" type="text" value="<?php echo $f12; ?>" /></th>
      </tr>
      <tr>
        <th>UPC</th>
        <th><input name="upc" id="upc" type="text" value="<?php echo $f13; ?>" /></th>
      </tr>
      <tr>
        <th>Update record</th>
        <th><a href="?update=true?ID=<?php echo $f1; ?>">Update</a></th>
      </tr>
      <?php
	$i++;
	}
	?>
    </table>
    <?php
	}
	else
	{
		echo ('Empty table');
	}
	?>
  </div>
  <br class="clearfloat" />
  <div id="footer">
<?php include('footer.php') ?>
  </div>
</div>
</body>
</html>

There is my entire page so far.

The Midnighter 0 Newbie Poster · Answer 4 · 2009-04-15T19:03:04+00:00

I see what you're saying.
Ok, so I've turned it into a 'proper form', as you can see:

<table border="1" cellpadding="2" cellspacing="2">
      <form onsubmit="" method="post" action="?update=true">
        <tr>
          <th>ID</th>
          <th><?php echo $f1; ?></th>
        </tr>
        <tr>
          <th>Invoice Date</th>
          <th><input name="invoiceDate" id="invoiceDate" type="text" value="<?php echo $f2; ?>" /></th>
        </tr>
        <tr>
          <th>Supplier</th>
          <th><SELECT name="supplier">
              <?php while($row = mysql_fetch_array($supResult)) { echo "<OPTION VALUE=".$row['supplier'].">".$row['supplier']."</OPTION>" ; } ?>
            </SELECT></th>
        </tr>
        <tr>
          <th>Manufacturer</th>
          <th><SELECT name="manufacturer">
              <?php while($row = mysql_fetch_array($manuResult)) { echo "<OPTION VALUE=".$row['Manufacturer'].">".$row['Manufacturer']."</OPTION>" ; } ?>
            </SELECT></th>
        </tr>
        <tr>
          <th>Invoice</th>
          <th><input name="invoice" id="invoice" type="text" value="<?php echo $f4; ?>" /></th>
        </tr>
        <tr>
          <th>Model</th>
          <th><input name="model" id="model" type="text" value="<?php echo $f6; ?>" /></th>
        </tr>
        <tr>
          <th>Description</th>
          <th><input name="description" id="description" type="text" value="<?php echo $f7; ?>" /></th>
        </tr>
        <tr>
          <th>Serial</th>
          <th><input name="serial" id="serial" type="text" value="<?php echo $f8; ?>" /></th>
        </tr>
        <tr>
          <th>Price</th>
          <th><input name="price" id="price" type="text" value="<?php echo $f9; ?>" /></th>
        </tr>
        <tr>
          <th>End user</th>
          <th><input name="endUser" id="endUser" type="text" value="<?php echo $f10; ?>" /></th>
        </tr>
        <tr>
          <th>Customer price</th>
          <th><input name="customerPrice" id="customerPrice" type="text" value="<?php echo $f11; ?>" /></th>
        </tr>
        <tr>
          <th>MEI Invoice</th>
          <th><input name="meiInvoice" id="meiInvoice" type="text" value="<?php echo $f12; ?>" /></th>
        </tr>
        <tr>
          <th>UPC</th>
          <th><input name="upc" id="upc" type="text" value="<?php echo $f13; ?>" /></th>
        </tr>
        <tr>
          <th>Update record</th>
          <th><a href="#" onclick="openwindow(this.href); return false;">Update</a></th>
        </tr>
        <?php
	$i++;
	}
	?>
      </form>
    </table>

So you're saying that I can now do this, am I right?

if (isset ($_REQUEST['update'])) 
{
$query = "UPDATE `tables2`SET column1=invoiceDate, column2=Supplier,etc WHERE `ID`=some_value";

}

Designer_101 -2 Posting Whiz · Answer 5 · 2009-04-15T19:05:26+00:00

I agree with JRsofty. Your best bet would be using an actuall form but having a <a href> and some javascript to submit it.

This can be done from:

<a href="?update=true" onClick='javascript:this.form.submit();'>Update</a>

Try that out, i'm not 100% because i've never had to do something like that however the code makes sense.

Hope this helps

Designer_101 -2 Posting Whiz · Answer 6 · 2009-04-15T19:07:31+00:00

Oh you've changed your method.
Either way is good so yes I think it would be fine.

Hope this helps

Updating MySQL ... tricky

Recommended Answers Collapse Answers

All 8 Replies

Recommended Answers