MySql mulitple row PHP update (Full Version)

All Forums >> [Web Development] >> General Web Development



Message

norwichdesign -> MySql mulitple row PHP update (11/17/2005 1:56:10)

Hi all,

I'm creating an update page that will update all the rows in a mysql db. At the moment it only updates the last row of the table. Any help is greatly received.

Code:

if (isset($_POST['submit']))
{
for ($i=0($_POST["id"]); $i++)
{
$theid = $_POST["id"][$i];
$qry = mysql_query("UPDATE products SET
products_kelkoo_date = '$_REQUEST[products_kelkoo_date]',
products_kelkoo_price = '$_REQUEST[products_kelkoo_price]',
products_price = '$_REQUEST[products_price]',
products_best_date = '$_REQUEST[products_best_date]',
products_best_price = '$_REQUEST[products_best_price]',
products_best_name = '$_REQUEST[products_best_name]',
products_notes = '$_REQUEST[products_notes]' WHERE products_id = $theid");
mysql_query($qry);
}
}
else
{

// read data from database
$result = mysql_query("select * from products order by products_model limit $rows")
or die ("Could not read data because ".mysql_error());

// print the data in a table
if (mysql_num_rows($result)) {
print "<div id=Main>";
print "<table cellpadding=2 cellspacing=0 border=1 width=\"1032\" bgcolor=#FFFBF0 style=border-color:#000066>\n";
print "<tr class=style4>

<td width=27 bgcolor=#3399FF>ID</td>
<td width=117 bgcolor=#3399FF>Make</td>
<td width=128 bgcolor=#3399FF>Model</td>
<td width=74 bgcolor=#3399FF>Kelkoo Price as of*</td>
<td width=74 bgcolor=#3399FF>Kelkoo Lowest Price</td>
<td width=55 bgcolor=#3399FF>Sell Price Incl VAT</td>
<td width=55 bgcolor=#3399FF>Special Price</td>
<td width=55 bgcolor=#3399FF>Kelkoo Price Diff</td>
<td width=74 bgcolor=#3399FF>Sell Price Ex VAT</td>
<td width=74 bgcolor=#3399FF>Buy Price as of*</td>
<td width=74 bgcolor=#3399FF>Best Buy Price</td>
<td width=85 bgcolor=#3399FF>Best Buy Company</td>
<td width=165 bgcolor=#3399FF>Product Notes</td>
</tr>";
while ($qry = mysql_fetch_array($result)) {

$id=$qry[manufacturers_id];

$manufactdetails = mysql_query("SELECT manufacturers_name FROM manufacturers WHERE manufacturers_id=$id");
$manufact = mysql_fetch_array($manufactdetails);
$manufacturers_name = $manufact[manufacturers_name];


$specialdetails = mysql_query("SELECT specials_new_products_price FROM specials WHERE products_id=$qry[products_id]");
$special = mysql_fetch_array($specialdetails);
$specials_price = $special[specials_new_products_price];

// define what % vat is
$vat = 17.5;

// work out the amount of vat
$price_with_vat = $qry[products_price] + ($vat*($qry[products_price]/100));

// Kelkoo difference sum
$products_diff = ($qry[products_kelkoo_price] - $price_with_vat);

// strings used in decimal point routine
$vatprice = $price_with_vat;
$price = $qry[products_price];
$numberkelkoo = $qry[products_kelkoo_price];

// Kelkoo difference to two decimal places
$kelkoo_diff = number_format($products_diff, 2, '.', '');

// VAT price to two decimal places
$products_price = number_format($price, 2, '.', '');

// Kelkoo price to two decimal places
$kelkooprice = number_format($numberkelkoo, 2, '.', '');

// VAT price to two decimal places
$products_vatprice = number_format($vatprice, 2, '.', '');


$specials_pricedec = number_format($specials_price, 2, '.', '');

// Display results
print "<form name=opscreen method=post action=$_SERVER[PHP_SELF]>";
print "<tr class=style3>
<td>$qry[products_id] <input name=id type=hidden value=$qry[products_id]></td>
<td>$manufacturers_name </td>
<td>$qry[products_model] </td>
<td><input name=products_kelkoo_date type=text value= $qry[products_kelkoo_date] size=6 ></td>
<td><input name=products_kelkoo_price type=text value= $kelkooprice size=6 ></td>
<td>$products_vatprice </td>
<td>$specials_pricedec </td>
<td>$kelkoo_diff </td>
<td><input name=products_price type=text value= $products_price size=5 ></td>
<td><input name=products_best_date type=text value= $qry[products_best_date] size=5 ></td>
<td><input name=products_best_price type=text value= $qry[products_best_price] size=5 ></td>
<td><input name=products_best_name type=text value= $qry[products_best_name] size=6 ></td>
<td><textarea rows=6 name=products_notes cols=12 >$qry[products_notes] </textarea></td>";
print "</td></tr>\n";
}
print "</table></div>\n";
print "<input type=submit name=submit value=Update />";
print "</form>";
}
}
?>



yogaboy -> RE: MySql mulitple row PHP update (11/17/2005 6:52:52)

Since you're using a for loop to run the update statement, have you tried outputting each update statement to the screen to check that they are actually created?



norwichdesign -> RE: MySql mulitple row PHP update (11/17/2005 9:13:38)

What do you mean



yogaboy -> RE: MySql mulitple row PHP update (11/17/2005 9:25:53)

for every time you go once round the loop you are executing 1 update statement. If you write (print, in the same way you print the table) that update statement to the screen you should end up with a screen full of update statements. That will allow you to see

a. How many statements are actually created by the code
b. Whether they are correct
c. you can compare them to the database and see whether any/some/all exceuted.

It's a bit like starting your pc in verbose mode so you can see all the drivers being loaded etc, as it helps you track down the bug!



norwichdesign -> RE: MySql mulitple row PHP update (11/17/2005 9:38:30)

Thanks for the replies, all i'm getting at the moment is the following error

Parse error: parse error, unexpected '(', expecting ';' in /home/samuicom/public_html/audiovisual/price.php on line 42

which is related to

for ($i=0($_POST["id"]); $i++)
{
$theid = $_POST["id"][$i];

Cheers bill



Tailslide -> RE: MySql mulitple row PHP update (11/17/2005 9:51:26)

Hi Bill

Unlike Yogaboy, I'm not a PHP/mySQL expert - I tend towards the "remove bits until it all works" school of coding! This is particularly effective with errors like the one you seem to have which is basically you've got a "(" it doesn't want and are missing a ";" somewhere too.

I'd start by taking out (s one at a time and see what the error message says. If it doesn't change, put that one back and remove another one. Hopefully you'll get rid of the unexpected "(" error and then it's a case of adding the ";" repeatedly until you get it in the right place.

I'm sure there's a better way of doing this - but being a learner myself this seems to work in the absence of logic!!



dpf -> RE: MySql mulitple row PHP update (11/17/2005 10:03:37)

quote:

for ($i=0($_POST["id"]); $i++)

the unexpected ( is here: $i=0( it is expecting the semi to seperate for elements. try this
for ($i=0;($_POST["id"]); $i++)



norwichdesign -> RE: MySql mulitple row PHP update (11/17/2005 10:04:45)

I've now got this code

if (isset($_POST['submit']))
{
$qry = mysql_query("UPDATE products SET
products_kelkoo_date = '$_REQUEST[products_kelkoo_date]',
products_kelkoo_price = '$_REQUEST[products_kelkoo_price]',
products_price = '$_REQUEST[products_price]',
products_best_date = '$_REQUEST[products_best_date]',
products_best_price = '$_REQUEST[products_best_price]',
products_best_name = '$_REQUEST[products_best_name]',
products_notes = '$_REQUEST[products_notes]' WHERE products_id = $id");
mysql_query($qry);

this only update the last row, i need help on making a loop or an array i think

cheers Bill



dpf -> RE: MySql mulitple row PHP update (11/17/2005 10:15:59)

quote:

this only update the last row, i need help on making a loop or an array i think
I was going to mention that this:
quote:

for ($i=0;($_POST["id"]); $i++)
wont work.
you establish a counter - i - and increment it but dont use it! the for loop has to also use that counter so that is loops and has an end. assuming you have multiple value of id to retrieve and process , you would use $_POST["id"] to place them into an array - lets say you call it myArray and then in your for loop - it would look like this

quote:

for ($i=0;(myArray[ i ];$i++)
now you are saying to loop thru the array and as the value of i increases, it processes the next until i reachs the end of the array length. does that help?



norwichdesign -> RE: MySql mulitple row PHP update (11/17/2005 10:26:35)

it's helping, and thanks for your time.

for ($i=0;(myArray[ i ];$i++) is giving me

Parse error: parse error, unexpected '[' in /home/samuicom/public_html/audiovisual/price.php on line 42



dpf -> RE: MySql mulitple row PHP update (11/17/2005 10:37:55)

well my javascript and ASP knowledge is fresher than my php so that may not be how youlist an array in php - sorry



dpf -> RE: MySql mulitple row PHP update (11/17/2005 10:40:47)

perhaps myArray[$i]
or
$myArray[$i]



norwichdesign -> RE: MySql mulitple row PHP update (11/17/2005 12:06:03)

tried possible combinations but all give errors



dpf -> RE: MySql mulitple row PHP update (11/17/2005 12:24:37)

what are the error messages now?



norwichdesign -> RE: MySql mulitple row PHP update (11/17/2005 12:27:00)



Parse error: parse error, unexpected '[' in /home/samuicom/public_html/audiovisual/price.php on line 42
or
Parse error: parse error, unexpected '(' in /home/samuicom/public_html/audiovisual/price.php on line 42
or
Parse error: parse error, unexpected ',' in /home/samuicom/public_html/audiovisual/price.php on line 42



yogaboy -> RE: MySql mulitple row PHP update (11/18/2005 7:16:35)

quote:

Unlike Yogaboy, I'm not a PHP/mySQL expert

I can't write php well at all, but I know c# (very similar). I do get lots of errors in my own code so perhaps I am an expert of sorts![:D]

My method with any bulk SQL insert/update is to write out the sql statements to the screen so I can check them over before I run them, and then go through the "remove it till it works"

I think Dan's really close, I think what you need is this
for ( $i=0; $_POST["id"] ; $i++)

you don't need the () around $_POST...

Or don't you try a foreach loop instead, since you aren't using the $i anyway?

To instatiate an array in php
$myArray = array("1st value", "2nd value");

To loop through
foreach ($myArray as $m)
{
print $m;
}

something like that.



norwichdesign -> RE: MySql mulitple row PHP update (11/18/2005 7:34:13)

I think its getting closer, now getting

Fatal error: Maximum execution time of 30 seconds exceeded in /home/samuicom/public_html/audiovisual/price.php on line 53



yogaboy -> RE: MySql mulitple row PHP update (11/20/2005 8:45:54)

That could be due the amount of update statements you are running, I've had that happen and I've ended up chopping it into several smaller chunks.

How many update statements are you doing?



crazybob -> RE: MySql mulitple row PHP update (11/28/2005 0:58:35)

This line:

set_time_limit(30);

will reset the timer to give your script another 30
seconds to complete before failing.

This line: set_time_limit(0); will turn off the timer
altogether.

HTH



Page: [1]

Valid CSS!




Forum Software © ASPPlayground.NET Advanced Edition 2.4.5 ANSI
0.078125