Page 1 of 1

Update statement not working properly

Posted: Sat Oct 18, 2008 10:38 am
by pingeyeg
I have run into a bit of a snag. I created this update statement, but I keep getting a return of "Query was empty" yet my SQL statement outputs to the screen what I am trying to do. Please take a look and let me know what I am doing wrong. Thanks

if($_POST['image-click']) {
$menuName = mysql_real_escape_string($menuName);
$menuLink = mysql_real_escape_string($menuLink);

$menuID = (int)$_POST['menuID'];
$menuName = $_POST['menuName'];
$menuLink = $_POST['menuLink'];

$updateMenu = "UPDATE menu
SET menuID = $menuID, menuName = '$menuName', menuLink = '$menuLink'
WHERE menuName = '$menuName'";
$menuUpdated = mysql_query($udpateMenu);
echo mysql_error();

$updatePage = "UPDATE pages SET pageID = $menuID, page = '$menuName' WHERE page = '$menuName'";
$pageUpdated = mysql_query($updatePage);
echo mysql_error();

if(mysql_affected_rows >= 1) {
$result = "The menu has been updated.";
} else {
$result = "There was an issue in completing your request.";
}
}

The page in question is at http://cms.goodboyweb.com/admin UN/ cmsuser PW/ cmsuser

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 10:41 am
by aceconcepts
You are missing single quotes from the following:

Code: Select all

 
SET menuID = $menuID
 
SET pageID = $menuID
 

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 10:44 am
by pingeyeg
Those are ID fields though, auto-increment. Aren't they supposed to not have single quotes around them since that is a number?

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 10:55 am
by aceconcepts
Try using mysql_error.

Code: Select all

mysql_query($udpateMenu) or die(mysql_error());

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 10:58 am
by pingeyeg
I already have that there:

$updateMenu = "UPDATE menu
SET menuID = '$menuID', menuName = '$menuName', menuLink = '$menuLink'
WHERE menuName = '$menuName'";
$menuUpdated = mysql_query($udpateMenu);
echo mysql_error();

That is where I am getting the "Query was empty" remark.

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 11:18 am
by pingeyeg
Ok, I have figure out one thing, but I am still having an issue. I had misspelled the variable calling the first query string. Now I no longer get that "Query was empty" remark, but the db is still not changing.

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 11:20 am
by aceconcepts
Have you checked to see the values of your variables before you send your query?

Try echoing your variables "$menuName" etc...

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 11:21 am
by pingeyeg
One step ahead of you. The following are my results for both queries:

UPDATE menu SET menuID = 3, menuName = 'Gallery', menuLink = 'gallery.php' WHERE menuName = 'Gallery'
UPDATE pages SET pageID = 3, page = 'Gallery' WHERE page = 'Gallery'

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 11:22 am
by pingeyeg
Actually, that might be my issue. The field that I am actually changing is not being displayed I the query. The one that is being displayed is the last field in the db. The following is my form.

<?php
if(isset($_POST['image-click'])) {
echo $result;
} elseif(!isset($_POST['image-click'])) {

$grabInfo = "SELECT menuID, menuName, menuLink FROM menu ORDER BY menuID ASC";
$outputInfo = mysql_query($grabInfo);
echo mysql_error();

while($row = mysql_fetch_assoc($outputInfo)) {
?>
Info for page <?= (int)$row['menuID'] ?>:
<div style="margin-bottom: 20px">
<input type="text" name="menuID" style="width: 25px" value="<?= (int)$row['menuID'] ?>"> <> <input type="text" name="menuName" style="width: 200px" value="<?= $row['menuName'] ?>"> <> <input type="text" name="menuLink" style="width: 200px" value="<?= $row['menuLink'] ?>">
</div>
<?php
}
}
?>

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 11:32 am
by aceconcepts
You're trying to send multiple form fields from within a while loop.

You need to send each field as an array:

Code: Select all

 
while($row = mysql_fetch_assoc($outputInfo)) {
?>
Info for page <?= (int)$row['menuID'] ?>:
<div style="margin-bottom: 20px">
<input type="text" name="menuID[]" style="width: 25px" value="<?= (int)$row['menuID'] ?>"> <> <input type="text" name="menuName[]" style="width: 200px" value="<?= $row['menuName'] ?>"> <> <input type="text" name="menuLink[]" style="width: 200px" value="<?= $row['menuLink'] ?>">
</div>
<?php
}
}
?>
 
Notice the square brackets i've placed at the end of each field name. This passes the form element/field as an array.

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 11:34 am
by pingeyeg
Is there something I need to place in between those brackets? Right now I am getting this for the queries:

UPDATE menu SET menuID = 1, menuName = 'Array', menuLink = 'Array' WHERE menuName = 'Array'
UPDATE pages SET pageID = 1, page = 'Array' WHERE page = 'Array'

Re: Update statement not working properly

Posted: Sat Oct 18, 2008 12:00 pm
by aceconcepts
When you submit the form you need to setup a for loop to get each of the fields submitted via the form:

Code: Select all

 
if($_POST['image-click']) {
$menuID = (int)$_POST['menuID'];
$menuName = $_POST['menuName'];
$menuLink = $_POST['menuLink'];
 
for($x=0; $x<count($menuID); $x++)
{
$menu_id=$menuID[$x];
$menu_name=$menuName[$x];
$menu_link=$menuLink[$x];
 
$updateMenu = "UPDATE menu
SET menuID = $menuID, menuName = '$menu_name', menuLink = '$menu_link'
WHERE menuName = '$menu_name'";
$menuUpdated = mysql_query($udpateMenu);
echo mysql_error();
 
$updatePage = "UPDATE pages SET pageID = '$menu_id', page = '$menu_name' WHERE page = '$menu_name'";
$pageUpdated = mysql_query($updatePage);
echo mysql_error();
}
 
if(mysql_affected_rows >= 1) {
$result = "The menu has been updated.";
} else {
$result = "There was an issue in completing your request.";
}
}