PHP Update MYQSL Database...should be simple

vjmehra · March 31, 2012

I am trying to do something that a hell of a lot of people must need to do every day, yet despite this I can not find a single example online, or in books, which makes absolutely no sense!

Anyway, hopefully it will be blindingly obvious to someone what I'm doing wrong, I really hope so as I can't figure it out...

I have a mysl table as follows:

tblvenue

venue_id PRIMARY KEY (int)

venue_name (varchar 50)

station_id (int)

I have a php/html page as follows:

<html>

<head>

<title>Update & Delete Venues</title>

</head>

<body marginheight="0" topmargin="0" marginwidth="0"

leftmargin="0" style="margin:0;padding:0" bgcolor="#B0E0E6">

<table border="1" style="border:solid black" cellpadding="0"

cellspacing="0" width="100%">

<tr>

<td align="center" width="33%">Action</td>

<td align="center" width="33%">Venue Name</td>

<td align="center" width="33%">Station Name</td>

</tr>

<?php

$station_name=$_POST['station_name'];

@ $db = new mysqli('XXX', 'XXX', 'XXX', 'XXX');

$query = "call sp_venue()";

$result = $db->query($query);

$num_results = $result->num_rows;

function db_result_to_array($result){

//---------------------------------------------------------------------

$res_array = array();

for ($count=0; $row = $result->fetch_assoc(); $count++) {

$res_array[$count]=$row;

}

return $res_array;

}

//---------------------------------------------------------------------

function get_dataset($server, $user, $password, $database, $query7) {

//---------------------------------------------------------------------

$db1 = new mysqli($server, $user, $password, $database);

$query1 = $query7;

$result1 = $db1->query($query1);

$num_results = $result1->num_rows;

$result8 = db_result_to_array($result1);

return $result8;}

//---------------------------------------------------------------------

echo "<tr>";

echo "<td>";

echo "<table width='100%'><td width='33%' align='center'><input type='submit' id='btnupdate' name='btnupdate' value='Update & Delete' ></td>";

echo "<td width='33%' align='center'><input type='submit' name='btncancel'id='btncancel' value='Cancel Updates' ></td>";

echo "<td width='33%' align='center'><input type='submit' value='Add New Venue' name='btnadd'></td></table>";

echo "</td>";

echo "<td><input type='text' name='tbvenue_name'/></td>";

echo "<td>";

?>

<?php

$stat_array=get_dataset('XXX', 'XXX', 'XXX', 'XXX',"call sp_station");

foreach ($stat_array as $thisstat){

echo "<option value=\"".$thisstat['station_id']."\"";

echo ">".$thisstat['station_name']."</option>";

}

?>

</select>

<?php

echo "</td>";

echo "</tr>";

echo "<tr>";

while($row = $result->fetch_assoc())

{

echo "<td width='33%'>Delete:<input type='Checkbox' id='cbdelete[]' name='cbdelete[]' value='".$row['venue_id'];

echo "'>Update:<input type='Checkbox' id='value' name='cbupdate[]' value='".$row['venue_id']."'><input type='hidden' name='venue_id[]' value='".$row['venue_id']."'></td>";

echo "<td width='33%'><input type='Textbox' width='100%' name='venue_names' value='" .$row['venue_name']. "'></td>";

echo "<td width='33%'>" ;

echo "<select name='station_id[]' width='100%'>";

$stat_array=get_dataset('localhost', 'root', 'snooker1', 'pool',"call sp_station");

foreach ($stat_array as $thiscat)

{

echo '<option value="' . $thiscat['station_id'] . '"';

if ($row['station_id'] == $thiscat['station_id']) {echo ' selected';}

echo '>' . $thiscat['station_name'] . '</option>';

}

echo "</td>";

echo "</tr>";

}

echo "</table>";

?>

</form>

</body>

</html>

This displays correctly and when checking the appropriate checkbox and clicking the script venue4.php is run:

<?php

//Connects to the database

$db = new mysqli('XXX', 'XXX', 'XXX', 'XXX');

//Checks to see if the update button has been clicked

if (isset($_POST['btnupdate']))

{

//Assigns checkbox array to variable

$aDel = $_POST['cbdelete'];

$aVal = $_POST['cbupdate'];

//Loops through rows to delete selected records

foreach($_POST['cbdelete'] as $delete11)

{

//Deletes record

$query = "delete from tblvenue where venue_id =".$delete11;

$result = $db->query($query);

$num_results = $result->num_rows;

}

//Loops through rows to delete selected records

foreach($_POST['cbupdate'] as $update11)

{

//Gets variable

//$Venue = $_POST['venue_names'];

//Updates record

$query = "update tblvenue set venue_name=".$venue." where venue_id =".$update11;

$result = $db->query($query);

$num_results = $result->num_rows;

}

//Checks to see if Add button was clicked

} elseif (isset($_POST['btnadd'])) {

//Looks for Venue Name & Station Name

$tbvenue_name=$_POST['tbvenue_name'];

$tbstat_id=$_POST['stat_id'];

//Formats variables

if(!get_magic_quotes_gpc()) {

$tbvenue_name = addslashes($tbvenue_name);

$tbstat_id = addslashes($tbstat_id);

}

//Inserts new record

$query = "call sp_insert_venue('".$tbvenue_name."',".$tbstat_id.")";

$result = $db->query($query);

} elseif (isset($_POST['btnedit'])) {

switch($page) {

default:

include("venue2.php");

break;

}

} else {

//If cancel button is clicked, goes straight to switch code below

}

//Closes database connection

$db->close();

//Goes back to Venue page

switch($page) {

default:

include("venue.php");

break;

}

echo var_dump($aval);

print_r($aVal);

//$test = $_POST['cbupdate'];

?>

Deleting records works fine.

Inserting records works fine.

If I hard code the data (ie. set venue_name = 'blah blah blah') for updates, I can get the relevant row id from cbupdate and update the row in question. However I can not get the data from venue2.php (typed into a textbox on the row in question) and submit this to the database to be updated.

This scenario must come up for zillions of developers, but so far no-one has been able to help.

Please can anyone assist...

vjmehra · March 31, 2012

In case it helps, I should have been clearer, I am not concerned with the SQL aspect, that part I understand :-)

when I ver dump $_POST, everything shows, but I'm not really sure how to construct anything from it, here is the dump (the last two checkboxes were selected, hence the final array):

array(7) { ["btnupdate"]=> string(15) "Update & Delete" ["tbvenue_name"]=> string(0) "" ["stat_id"]=> string(1) "1" ["venue_id"]=> array(6) { [0]=> string(1) "3" [1]=> string(1) "2" [2]=> string(1) "4" [3]=> string(1) "5" [4]=> string(1) "1" [5]=> string(2) "41" } ["venue_names"]=> string(4) "test" ["station_id"]=> array(6) { [0]=> string(1) "1" [1]=> string(1) "4" [2]=> string(1) "2" [3]=> string(1) "3" [4]=> string(1) "5" [5]=> string(1) "1" } ["cbupdate"]=> array(2) { [0]=> string(1) "1" [1]=> string(2) "41" } }

I guess my issue is that I have no idea how to turn all of that into what I need!

Any help is much appareciated!

Muddy_Funster · April 1, 2012

As you are so comfortable with SQL, you know how important it is to quote your strings...

Drummin · April 1, 2012

//Updates record
$query = "update tblvenue set venue_name='$venue' where venue_id ='$update11'";
$result = $db->query($query);
$num_results = $result->num_rows;

vjmehra · April 1, 2012

haha yes true that was a pretty simple oversight on my part, however that simply updates the record to '', so it appears the string is not correctly getting passed for some reason...

Muddy_Funster · April 2, 2012

so now you need to find out why $venue is empty.

Hint: PHP is very case sensitive...

vjmehra · April 2, 2012

I think the problem is more than just that, I'm trying to submit an array and wasn't doing this. I've now altered the code slightly, taking the $_POST variable I get the following from Print_r:

Update & Delete1Array ( [0] => 41 ) Array ( [0] => 41 [1] => l [2] => 3 [3] => Barcelo Daventry [4] => 2 [5] => Mile End Snooker Club [6] => 4 [7] => Shepherds Bush Rileys [8] => 5 [9] => Solihull Rileys [10] => 1 [11] => St Albans Hurricane Room ) Array ( [0] => 1 [1] => 1 [2] => 4 [3] => 2 [4] => 3 [5] => 5 )

Therefore I can extract the index of each item and logically I would assume in the above example I should be able to take [0] as the only ticked box and extract the relevant data, although it doesn't fully make sense to me, as index [0] from above returns the ID 41 twice and then returns the text 'l' as [1] (whereas this text was input against ID 41).

So now I'm confused again :-(

Drummin · April 2, 2012

//$Venue = $_POST['venue_names'];

//Updates record
$query = "update tblvenue set venue_name=".$venue." where venue_id =".$update11;
$result = $db->query($query);
$num_results = $result->num_rows;

Should be

$venue = $_POST['venue_names'];

//Updates record
$query = "update tblvenue set venue_name=".$venue." where venue_id =".$update11;
$result = $db->query($query);
$num_results = $result->num_rows;

vjmehra · April 8, 2012

/$Venue = $_POST['venue_names'];

Was commented out as I was playing around with the code, obviously I should have uncommented that before posting the code, but even with that, it still doesn't work.

In essence, I don't understand how to get the data submitted when the button is clicked in any meaningfull format. I can get a var_dump of $_POST, so the data is being sent, however if only one checkbox is ticked for example, then there may be only 1 row in that particular array, so I can't easily match that up to the array sent for venue_name...

Drummin · April 9, 2012

When looking at this situation, it came down to using checkboxes by each record and having one submit button at the bottom or wrapping each record in a form and each having a delete and update button. For usability sake, I opted for the second one, as user can just modify field and click update or delete. And besides, you already had buttons by each record. Sure wrapping a form around each table does use some resources but I did remove a bunch of DB queries by not querying the station table for every record. This sample adds new venues, updates or deletes existing ones, All on one page. Sure it's different than what you had going, but I think it will do the job for you. If you need the first option that's not hard to do. Just need a select checkbox by each record and single update and delete buttons at the bottom.

<?php
$host = "";
$login = "";
$dbpass = "";
$db = "";
//Make connection to DB
mysql_connect("$host","$login","$dbpass") OR DIE
        ("There is a problem with the system.  Please notify your system administrator." .mysql_error());

mysql_select_db("$db") OR DIE
        ("There is a problem with the system.  Please notify your system administrator." .mysql_error());
// do some house keeping //
//adds slashes if the magic quotes is off.
function addslash($string)
{
   if (!get_magic_quotes_gpc())
       $string = addslashes($string);
   return $string;
}
if (get_magic_quotes_gpc())
{
function stripslashes_deep($value)
{
$value = is_array($value) ?
array_map('stripslashes_deep', $value) :
stripslashes($value);
return $value;
}
$_POST = array_map('stripslashes_deep', $_POST);
$_GET = array_map('stripslashes_deep', $_GET);
$_COOKIE = array_map('stripslashes_deep', $_COOKIE);
$_REQUEST = array_map('stripslashes_deep', $_REQUEST);
}
//Delete
if (isset($_POST['Delete'])){
mysql_query("DELETE FROM tblvenue WHERE venue_id='{$_POST['venue_id']}'");
}
//New
if (isset($_POST['NewVenue'])){
if (!empty($_POST['newstat_id']) && !empty($_POST['newtbvenue_name'])){
$newtbvenue_name = mysql_real_escape_string(trim($_POST['newtbvenue_name']));
mysql_query("INSERT INTO tblvenue (venue_name,station_id) VALUES('$newtbvenue_name','{$_POST['newstat_id']}')");
}
}
//Update
if (isset($_POST['Update'])){
if (!empty($_POST['stat_id']) && !empty($_POST['tbvenue_name'])){
$tbvenue_name = mysql_real_escape_string(trim($_POST['tbvenue_name']));
mysql_query("UPDATE tblvenue SET venue_name='$tbvenue_name', station_id='{$_POST['stat_id']}' WHERE venue_id='{$_POST['venue_id']}'");
}
}
//create array for stations so query is not run with every record
$stations = array();
$sqls="SELECT * FROM `call sp_station` ";
$results=mysql_query($sqls);
while($stat=mysql_fetch_array($results)){
$stations[$stat['station_id']]=$stat['station_name'];
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Update & Delete Venues</title>
<style type="text/css">
body{
margin:0;
padding:0;
background-color:#B0E0E6;
}
.tablestyle{
border:1px solid black;
margin:0; 
border-collapse:collapse;
padding:0;
}
</style>
</head>
<body>

<?php
echo "<form action=\"\" method=\"post\" style=\"padding:0 margin:0\">\r";
echo "<table border=\"1\" class=\"tablestyle\" cellpadding=\"0\"
cellspacing=\"0\" width=\"100%\" style=\"padding:0 margin:0\">\r";
	echo "<tr>\r";
		echo "<td align=\"center\" width=\"33%\">Action</td>\r";
		echo "<td align=\"center\" width=\"33%\">Venue Name</td>\r";
		echo "<td align=\"center\" width=\"33%\">Station Name</td>\r";
	echo "</tr>\r";
	echo "<tr>\r";
		echo "<td align=\"center\"><input type='submit' value='Add New Venue' name='NewVenue' style=\"width:100%;\" /></td>\r";
		echo "<td align=\"center\"><input type='text' name='newtbvenue_name' style=\"width:100%;\" /></td>\r";
		echo "<td align=\"center\">\r";
			echo "<select name=\"newstat_id\" style=\"width:100%;\">\r";
			echo  "<option value=\"\"> - Select - </option>\r";
			if (!empty($stations)){
				foreach($stations as $k => $v){
				echo  "<option value=\"$k\">$v</option>\r";
				}
			}
			echo "</select>\r";	
		echo "</td>\r"; 
	echo "</tr>\r";
echo "</table>\r";
echo "</form>\r";

//---------------------------------------------------------------------	
//Critics of *, this table only has three fields, all used here.
$sql="SELECT * FROM `tblvenue` ORDER BY venue_name ASC";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)){
echo "<form action=\"\" method=\"post\" style=\"padding:0 margin:0\">\r";
echo "<table border=\"1\" class=\"tablestyle\" cellpadding=\"0\"
cellspacing=\"0\" width=\"100%\" style=\"padding:0 margin:0\">\r";
	echo "<tr>\r";
		echo "<td width=\"33%\">\r<input type=\"hidden\" name=\"venue_id\" value=\"{$row['venue_id']}\" />\r";
		echo "<div style=\"width:50%; text-align:center;float:left;\">\r<input type=\"submit\" name=\"Delete\" value=\"Delete\" style=\"width:100%;\" />\r</div>\r";
		echo "<div style=\"width:50%; text-align:center;float:left;\">\r<input type=\"submit\" name=\"Update\"  value=\"Update\" style=\"width:100%;\" />\r</div>\r";
		echo "</td>\r";
		echo "<td width=\"33%\">\r<input type='text' name='tbvenue_name' value=\"".$row['venue_name']."\" style=\"width:100%;\" /></td>\r";
		echo "<td width=\"33%\">\r";
		echo "<select name=\"stat_id\" style=\"width:100%;\">\r";			
			if (!empty($stations)){
				foreach($stations as $k => $v){
				echo  "<option value=\"$k\"" . ($row['station_id']=="$k" ? ' selected="selected"' : '') . ">$v</option>\r";
				}
			}
		echo "</select>\r";
		echo "</td>\r";
	echo "</tr>\r";
echo "</table>\r";
echo "</form>\r";
}
?>
</body>
</html>

vjmehra · April 10, 2012

I tried this, but your code sample doesn't seem to work, not sure if maybe there are a few ' or " in the wrong places maybe?

Drummin · April 10, 2012

Sorry, It worked perfectly for me and I doubt it's a matter of the way I coded it. Can I assume you added DB info at the top of the page? I was a little suspicious of the table named `call sp_station` as there's a space in the name. Do the table names match what you have? Any errors shown? In what way doesn't it work?

vjmehra · April 10, 2012

Yep I added the Db Info, 'call sp_station' is a stored procedure rather than a table.

Not sure how well this will work, but this is what it outputs:

\r"; echo "\r"; echo " \r"; echo " Action \r"; echo " Venue Name \r"; echo " Station Name \r"; echo "

\r"; echo " \r"; echo " \r"; echo " \r"; echo " \r"; echo "\r"; echo "- Select -\r"; if (!empty($stations)){ foreach($stations as $k => $v){ echo "$v\r"; } } echo "\r"; echo " \r"; echo "

\r"; echo " \r";echo "\r";//--------------------------------------------------------------------- //Critics of *, this table only has three fields, all used here.$sql="SELECT * FROM `tblvenue` ORDER BY venue_name ASC";$result=mysql_query($sql);while($row=mysql_fetch_array($result)){echo "

\r"; echo "\r"; echo " \r"; echo " \r\r"; echo "

\r\r

\r"; echo "

\r\r

\r"; echo " \r"; echo " \r \r"; echo " \r"; echo "\r"; if (!empty($stations)){ foreach($stations as $k => $v){ echo "$v\r"; } } echo "\r"; echo " \r"; echo "

\r"; echo " \r";echo "

\r";}?>

Drummin · April 10, 2012

OK then how does it work putting your procedure back in?

<?php
$host = "";
$login = "";
$dbpass = "";
$db = "";
//Add session_start to top of each page//
session_start();
//Make connection to DB
mysql_connect("$host","$login","$dbpass") OR DIE
        ("There is a problem with the system.  Please notify your system administrator." .mysql_error());

mysql_select_db("$db") OR DIE
        ("There is a problem with the system.  Please notify your system administrator." .mysql_error());
// do some house keeping //
//adds slashes if the magic quotes is off.
function addslash($string)
{
   if (!get_magic_quotes_gpc())
       $string = addslashes($string);
   return $string;
}
if (get_magic_quotes_gpc())
{
function stripslashes_deep($value)
{
$value = is_array($value) ?
array_map('stripslashes_deep', $value) :
stripslashes($value);
return $value;
}
$_POST = array_map('stripslashes_deep', $_POST);
$_GET = array_map('stripslashes_deep', $_GET);
$_COOKIE = array_map('stripslashes_deep', $_COOKIE);
$_REQUEST = array_map('stripslashes_deep', $_REQUEST);
}
//Delete
if (isset($_POST['Delete'])){
mysql_query("DELETE FROM tblvenue WHERE venue_id='{$_POST['venue_id']}'");
}
//New
if (isset($_POST['NewVenue'])){
if (!empty($_POST['newstat_id']) && !empty($_POST['newtbvenue_name'])){
$newtbvenue_name = mysql_real_escape_string(trim($_POST['newtbvenue_name']));
mysql_query("INSERT INTO tblvenue (venue_name,station_id) VALUES('$newtbvenue_name','{$_POST['newstat_id']}')");
}
}
//Update
if (isset($_POST['Update'])){
if (!empty($_POST['stat_id']) && !empty($_POST['tbvenue_name'])){
$tbvenue_name = mysql_real_escape_string(trim($_POST['tbvenue_name']));
mysql_query("UPDATE tblvenue SET venue_name='$tbvenue_name', station_id='{$_POST['stat_id']}' WHERE venue_id='{$_POST['venue_id']}'");
}
}
?>
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN"
        "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<title>Update & Delete Venues</title>
<style type="text/css">
body{
margin:0;
padding:0;
background-color:#B0E0E6;
}
.tablestyle{
border:1px solid black;
margin:0; 
border-collapse:collapse;
padding:0;
}
</style>
</head>
<body>

<?php
echo "<form action=\"\" method=\"post\" style=\"padding:0 margin:0\">\r";
echo "<table border=\"1\" class=\"tablestyle\" cellpadding=\"0\"
cellspacing=\"0\" width=\"100%\" style=\"padding:0 margin:0\">\r";
	echo "<tr>\r";
		echo "<td align=\"center\" width=\"33%\">Action</td>\r";
		echo "<td align=\"center\" width=\"33%\">Venue Name</td>\r";
		echo "<td align=\"center\" width=\"33%\">Station Name</td>\r";
	echo "</tr>\r";
	echo "<tr>\r";
		echo "<td align=\"center\"><input type='submit' value='Add New Venue' name='NewVenue' style=\"width:100%;\" /></td>\r";
		echo "<td align=\"center\"><input type='text' name='newtbvenue_name' style=\"width:100%;\" /></td>\r";
		echo "<td align=\"center\">\r";
			echo "<select name=\"newstat_id\" style=\"width:100%;\">\r";
			echo  "<option value=\"\"> - Select - </option>\r";
			$stat_array=get_dataset('localhost', 'root', 'snooker1', 'pool',"call sp_station");
foreach ($stat_array as $thiscat)
{
    echo '<option value="' . $thiscat['station_id'] . '"';
    if ($row['station_id'] == $thiscat['station_id']) {echo ' selected';}
    echo '>' . $thiscat['station_name'] . '</option>';
}				
			echo "</select>\r";	
		echo "</td>\r"; 
	echo "</tr>\r";
echo "</table>\r";
echo "</form>\r";

//---------------------------------------------------------------------	
//Critics of *, this table only has three fields, all used here.
$sql="SELECT * FROM `tblvenue` ORDER BY venue_name ASC";
$result=mysql_query($sql);
while($row=mysql_fetch_array($result)){
echo "<form action=\"\" method=\"post\" style=\"padding:0 margin:0\">\r";
echo "<table border=\"1\" class=\"tablestyle\" cellpadding=\"0\"
cellspacing=\"0\" width=\"100%\" style=\"padding:0 margin:0\">\r";
	echo "<tr>\r";
		echo "<td width=\"33%\">\r<input type=\"hidden\" name=\"venue_id\" value=\"{$row['venue_id']}\" />\r";
		echo "<div style=\"width:50%; text-align:center;float:left;\">\r<input type=\"submit\" name=\"Delete\" value=\"Delete\" style=\"width:100%;\" />\r</div>\r";
		echo "<div style=\"width:50%; text-align:center;float:left;\">\r<input type=\"submit\" name=\"Update\"  value=\"Update\" style=\"width:100%;\" />\r</div>\r";
		echo "</td>\r";
		echo "<td width=\"33%\">\r<input type='text' name='tbvenue_name' value=\"".$row['venue_name']."\" style=\"width:100%;\" /></td>\r";
		echo "<td width=\"33%\">\r";
		echo "<select name=\"stat_id\" style=\"width:100%;\">\r";			
	$stat_array=get_dataset('localhost', 'root', 'snooker1', 'pool',"call sp_station");

foreach ($stat_array as $thiscat)
{
    echo '<option value="' . $thiscat['station_id'] . '"';
    if ($row['station_id'] == $thiscat['station_id']) {echo ' selected';}
    echo '>' . $thiscat['station_name'] . '</option>';
} 
		echo "</select>\r";
		echo "</td>\r";
	echo "</tr>\r";
echo "</table>\r";
echo "</form>\r";
}
?>
</body>
</html>

Maq · April 10, 2012

vjmehra, in the future, place

OR

tags around your code.

Drummin · April 10, 2012

Vjmehra, in case you want to go with my original code, I had just created a simple table for stations.

CREATE TABLE IF NOT EXISTS `call sp_station` (
  `station_id` int(11) NOT NULL AUTO_INCREMENT,
  `station_name` varchar(50) NOT NULL,
  PRIMARY KEY (`station_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Sign In

PHP Update MYQSL Database...should be simple

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information