PHP and MYSQL Update Multiple rows


#1

Hi, i have been stuc on this script literally for over 5 years. I’ve asked different forums year ago, but nobodys’ answer helped, so im finaly giving it another go. I have a small portion of the code I re did from scratch on a new document, but it wont work. I am trying to update with a form

I want to be able to clikc theo ne submit button at the button, but there is one text box for every row in my db that the “location” s set to 2.

basically iits somewhat like this:

name image - (textare for price)
name image (text area for price

SUBMIT

when i press submit it updates all of the price box to “Array”

here is my code:
[php][$uprice = $_POST[‘update’];
$updates = $_POST[‘price’];

if(!isset($uprice)) {
$get1 = “SELECT * FROM uitems WHERE username=’$suserid’ AND location=‘2’”;
$get = mysql_query($get1);
while($data = mysql_fetch_array($get)) {

$id = $data[‘uitemid’];
$owner = $data[‘username’];
$item = $data[‘theitemid’];
$location = $data[‘location’];
$price = $data[‘price’];

$item1 = “SELECT * FROM items WHERE itemid=’$item’”;
$getits = mysql_query($item1);
while($thi = mysql_fetch_array($getits)) {

$theid = $thi[‘itemid’];
$name = $thi[‘name’];
$pic = $thi[‘image’];
$desc = $thi[‘description’];

echo “

$name ”;

}
}
echo “”;
echo “”;
}

if(isset($uprice)) {

mysql_query(“UPDATE uitems SET price=’$updates’ WHERE username=’$suserid’ AND location=‘2’”);
echo “yes”;
}/php]]
again, even if i manually update the prices in the DB, and change them to what i want to, it shows in the textarea and database to “Array” if you need moren info before helping me please ask, if not can you please help me find a solution? ive been programming different sites for over 14 years now and this one has stumped me for years,. thank you in advance :slight_smile:


#2

Your field for the price creates an array
[php]name=‘price[]’[/php]

Which will result in a POST like this (if you submit 100 in the text field)
[php]$_POST[‘price’] = [
0 => 100
];[/php]

Which if echoed will return “Array” in the text box.

Since you create one form for each item I’d suggest you remove the [] from the name so you’re only submitting one price. Then add a hidden field to each form containing the items uitemid (or whichever it is you need) so you can update the price WHERE id = $_POST[‘id’]. This way you should be setting only one numerical value to only one row in the table. Atm you’re trying to set an array of one value to ALL the rows in the table

[hr]

This code is using mysql_* functions, all of these are removed in newer PHP versions. I strongly suggest you start using PDO or Mysqli with prepared and parameterized queries to avoid sql injection hacks. At least this should not be publically available if it contains privacy concerned data. Or if it’s running on a machine you do not own (sql injection hacks could let the attacker take over the server).

[hr]

I also suggest you separate PHP and HTML. It will make your editor able to parse it correctly so you get syntax highlighting and linting, which is great. On top of that it’s simply easier to work with as you don’t have nested quotes as you might have when echoing HTML with PHP.

Using tables for layout is considered bad practise and you seem to create a lot of local variables (ie $updates = $_POST[‘price’]:wink: that aren’t really necessary.

I’d also recommend you move the update logic to its own file and use javascript to call that dynamically so you can update prices without reloading the page.


#3

hi, im still puzzled as your solution to this if you can help me out the lines on this part of the code arent really too much here is what ive edited it, and the submit sends the “yes” which i usually put telling me no error and it submitted the code, but the text box for prices are blank, here the code is:

[php]$uprice = $_POST[‘update’];
$updates = $_POST[‘prices’];

if(!isset($uprice)) {
$get1 = “SELECT * FROM uitems WHERE username=’$suserid’ AND location=‘2’”;
$get = mysql_query($get1);
while($data = mysql_fetch_array($get)) {

$id = $data[‘uitemid’];
$owner = $data[‘username’];
$item = $data[‘theitemid’];
$location = $data[‘location’];
$price = $data[‘price’];

$item1 = “SELECT * FROM items WHERE itemid=’$item’”;
$getits = mysql_query($item1);
while($thi = mysql_fetch_array($getits)) {

$theid = $thi[‘itemid’];
$name = $thi[‘name’];
$pic = $thi[‘image’];
$desc = $thi[‘description’];

echo “

$name ”;

}
}
echo “”;
echo “”;
}

if(isset($uprice)) {

mysql_query(“UPDATE uitems SET price=’$updates’ WHERE username=’$suserid’ AND location=‘2’”);
echo “yes”;
}[/php]

this is only the part of the code im having issues with, everything else works fine. ive had to enter random prices through the actual phpmyadmin DB. to test it out, all works well. im not sure how this would get fixed ive been stuck on this for years ive started fresh even a few times on this page. thank you for the reply i appreciate, and i havent programmed for about a year and a half and i know php changes im so used to using it this way, i started programming whne i was 11 so im kind of used to this. ill read more into the new stuff, ill get some books at the book store thank you for letting me know my whole website is sadly full of this code. lol so anyway if you can continue or have a quick minute how do i solve this issue if you know thank you :slight_smile:


#4

Note that the code below is untested. Back up your code before messing with it.

ushops.php
Changed this file to show the item list only. The new SQL joins in the items table so you get every column you need in a single query instead of N+1 as you originally had (one extra query for every result).
[php]

<?php $sql = 'SELECT ui.uitemid, i.image, i.name, ui.price FROM uitems AS ui LEFT JOIN items AS i ON ui.theitemid = i.itemid WHERE ui.username="'. $suserid .'" AND ui.location="2"'; $itemsQuery = mysql_query($sql); while ($item = mysql_fetch_array($itemsQuery)): ?> <?= $item['name'] ?> <?php endwhile; ?>

[/php]

item_update_price.php
New file! This is only responsible for updating the price. We get the uitemid from the hidden field in the form, and the price from the text field. The response codes are so our JS handler understands when something went wrong
[php]

<?php // needs some logic to get $suserid and to run mysql_connect $uitemid = isset($_POST['uitemid']) ? $_POST['uitemid'] : null; $price = isset($_POST['price']) ? $_POST['price'] : null; if (!$uitemid || !$price) { http_response_code(400); exit(); } $sql = "UPDATE uitems SET price='$price' WHERE uitemid = '$uitemid' AND username='$suserid' AND location='2'"; if (!mysql_query($sql)) { http_response_code(500); exit(); } [/php] item.js New file! This JS code requires Jquery, but can be written in plain JS if you don't use Jquery (I just assumed you did). It basically just listens for the form(s) to be submitted. And when they are it stops the browser from submitting the form (preventDefault), it then disabled the submit button and updates its text, fires off a request to our item_update_price.php file with the form data ($(this).serialize() pulls the values out of the form). And then enables the submit button again afterwards. [php] $(function(){ $("form.item_update_price").submit(function(e) { e.preventDefault(); var $submitbutton = $(this).find('input[type="submit"]'); $submitbutton.attr('disabled', true).val('updating...'); $.post("item_update_price.php", $(this).serialize()) .fail(function() { alert("error"); }) .always(function() { $submitbutton.attr('disabled', false).val('update'); }); }); }); [/php] [hr] disclaimer: no sql injection or xsrf protection, pretty much no error handling, no error logging, no output escaping... in other words a lot of work left in this

#5

hi i am working my OVNT job right now, here to reply i will test these in the am, thank you both of you for taking time to try and help and for doing this, i will post how it turned out, and your comment on backing up my code made me laugh, its not like ill lose much, lol. i have backups saved on my pc and the other parts work, but i ha to crack a joke. thank you so much guys ill post when i get home :slight_smile: i think ive posted on this forum like years ago but i lost my login, and all of my posts were for this issue