Update statement not working properly
Moderator: General Moderators
Update statement not working properly
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
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
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Update statement not working properly
You are missing single quotes from the following:
Code: Select all
SET menuID = $menuID
SET pageID = $menuID
Re: Update statement not working properly
Those are ID fields though, auto-increment. Aren't they supposed to not have single quotes around them since that is a number?
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Update statement not working properly
Try using mysql_error.
Code: Select all
mysql_query($udpateMenu) or die(mysql_error());Re: Update statement not working properly
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.
$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
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.
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Update statement not working properly
Have you checked to see the values of your variables before you send your query?
Try echoing your variables "$menuName" etc...
Try echoing your variables "$menuName" etc...
Re: Update statement not working properly
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'
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
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
}
}
?>
<?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
}
}
?>
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Update statement not working properly
You're trying to send multiple form fields from within a while loop.
You need to send each field as an array:
Notice the square brackets i've placed at the end of each field name. This passes the form element/field as an array.
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
}
}
?>
Re: Update statement not working properly
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'
UPDATE menu SET menuID = 1, menuName = 'Array', menuLink = 'Array' WHERE menuName = 'Array'
UPDATE pages SET pageID = 1, page = 'Array' WHERE page = 'Array'
- aceconcepts
- DevNet Resident
- Posts: 1424
- Joined: Mon Feb 06, 2006 11:26 am
- Location: London
Re: Update statement not working properly
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.";
}
}