[SOLVED] Updating database records

joshgarrod · June 8, 2009

I have a form which form which should display the information from a specific record (selected by user clicking a link on page before). All I am getting is the following error, and the form fields aren't displying the information they should be to update.

I am getting the following error, I have had it before and I cannot remember what I did to fix it, the code I am using is old so that could be the reason, does anyone know how I can fix this please?

Error:

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in C:\Users\website\spares_edit_part.php on line 65

And the same for lines up to 70

Code:

<?php
  
  $idir = "uploads/";   // Path To Images Directory


if (isset ($_FILES['fupload'])){

//upload the image to tmp directory
$url = $_FILES['fupload']['name'];   // Set $url To Equal The Filename For Later Use 
	if ($_FILES['fupload']['type'] == "image/jpg" || $_FILES['fupload']['type'] == "image/jpeg" || $_FILES['fupload']['type'] == "image/pjpeg") { 
		$file_ext = strrchr($_FILES['fupload']['name'], '.');   // Get The File Extention In The Format Of , For Instance, .jpg, .gif or .php 
		$copy = copy($_FILES['fupload']['tmp_name'], "$idir" . $_FILES['fupload']['name']);   // Move Image From Temporary Location To Permanent Location 
			}
			}
  
error_reporting (E_ALL ^ E_NOTICE);
    $usr = "usr";
    $pwd = "pass";
    $db = "db";
    $host = "srv";

$var = @$_GET['ref'] ;
  $trimmed = trim($var);
  
    # connect to database
    $cid = mysql_connect($host,$usr,$pwd);
    if (!$cid) { echo("ERROR: " . mysql_error() . "\n");    }

     $query = "SELECT * FROM spares where ref like \"%$trimmed%\"";
     $result = mysql_query($query);  


$title=mysql_result($result,0,"title");
$descr=mysql_result($result,0,"descr");
$partno=mysql_result($result,0,"partno");
$price=mysql_result($result,0,"price");
$avail=mysql_result($result,0,"avail");
$image=mysql_result($result,0,"image");

    # this is processed when the form is submitted
    # back on to this page (POST METHOD)
    if ($_SERVER['REQUEST_METHOD']) {

$title=$_POST["title"];
$descr=$_POST["descr"];
$partno=$_POST["partno"];
$price=$_POST["price"];
$avail=$_POST["avail"];
$image=$_POST["image"];

        # double-up apostrophes
        $title = str_replace("'","''",$title);
        $descr = str_replace("'","''",$descr);
	$partno = str_replace("'","''",$partno);
	$price = str_replace("'","''",$price);
	$avail = str_replace("'","''",$avail);

        # setup SQL statement
	$SQL = "UPDATE `spares` SET `title` = '$title',`descr` = '$descr',`partno` = '$partno',`price` = '$price',`avail` = '$avail',`image` = '$image' WHERE `spares`.`ref` =$trimmed";

        #execute SQL statement
        $result = mysql_db_query($db,"$SQL",$cid);
	$ID=mysql_insert_id();


        # check for error
        if (!$result) { echo("ERROR: " . mysql_error() . "\n$SQL\n");    }

        echo ("<P>Part updated</P>\n");

    }

?>

It also prints "Part updated" too and it shouldn't do this unless it has actually been updated?

HTML:

<form enctype="multipart/form-data" action="<?php print $_SERVER['PHP_SELF'] ?> " method ="post">
   <div class="input_row">
     <div class="input_titles">Title:</div>
     <div class="input_box"><INPUT NAME="title" TYPE="text" id="title" VALUE="<?php echo $title; ?>" SIZE=50> 
       e.g. Avondale Dart Rooflight     </div>
   </div>
   <div class="input_row"><div class="input_titles">Description:<br /><br />(Use this space to add further descriptive text about this spare part e.g. dimensions etc.)</div><div class="input_box"><textarea name="descr" cols="50" rows="10" id="descr" type="text" value="<?php echo $descr; ?>"</textarea></div>
   </div>
   <div class="input_row"><div class="input_titles">Part number:</div><div class="input_box"><input name="partno" type="text" id="partno" value="<?php echo $partno; ?>" size="10" />
   </div></div>
   <div class="input_row"><div class="input_titles">Price (£) :</div><div class="input_box"><input name="price" type="text" id="price" value="<?php echo $price; ?>" size="15" />
   (number only, no commas or pound signs)</div></div>
   <div class="input_row"><div class="input_titles">In stock:</div><div class="input_box"><input name="avail" type="text" id="avail" value="<?php echo $avail; ?>" size="4" />
   (number only)</div></div>
   <input type = "hidden" name="MAX_FILE_SIZE" value = "102400">
   <div class="input_row"><div class="input_titles">Select image:</div><div class="input_box"><input type = "file" name = "fupload"></div></div>
   <div class="input_row"><div class="input_titles"><input name="submit" type="submit" value="Update part" /></div></div>
   </FORM>

MadTechie · June 8, 2009

and what database are you talking to ?

ie

$db_selected = mysql_select_db('foo', $cid);

joshgarrod · June 8, 2009

At which point would I need to use that, it seems to update the record when I load the page?

joshgarrod · June 8, 2009

I have just used some more test data and when I click the link to edit it has already updated the fields with blank information, I am also getting the following message when I click "update":

ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 1 UPDATE `spares` SET `title` = '',`descr` = '',`partno` = '',`price` = '',`avail` = '',`image` = '' WHERE `spares`.`ref` =

MadTechie · June 8, 2009

Well that suggests that

$_GET['ref'] ; isn't being set! your need to add that to your form

ie

<form enctype="multipart/form-data" action="<?php print $_SERVER['PHP_SELF']."?".$trimmed ?> " method ="post">

also theirs no quotes on the update line

$SQL = "UPDATE `spares` SET `title` = '$title',`descr` = '$descr',`partno` = '$partno',`price` = '$price',`avail` = '$avail',`image` = '$image' WHERE `spares`.`ref` ='$trimmed' ";

joshgarrod · June 8, 2009

I have changed the lines above, it doesn't show the error message, but it still updates the record with blank data on page load, and if i fill the form and and click update, it doesn't submit the update. The warning I mentioned first time are still showing on page load. thanks v much for your help

MadTechie · June 8, 2009

The page will always update.. thats what you wrote!

can you post a more complete script and i'll over give it the once over (i'm at work so kinda hard) to write too much on here!

joshgarrod · June 8, 2009

The first post I made was my complete script of the php and the form HTML, sorry, I am trying very hard to learn PHP but I always get in a muddle.

MadTechie · June 8, 2009

OKay heres a single script (untested)

if you call it test.php and open test.php?ref=something

it should open that record and allow you to edit it,

<?php
error_reporting (E_ALL ^ E_NOTICE);
$idir = "uploads/";   // Path To Images Directory
$usr = "usr";
$pwd = "pass";
$db = "db";
$host = "srv";

/***********************Upload File***********************/
if (isset ($_FILES['fupload']))
{
//upload the image to tmp directory
$url = $_FILES['fupload']['name'];   // Set $url To Equal The Filename For Later Use 
if ($_FILES['fupload']['type'] == "image/jpg" || $_FILES['fupload']['type'] == "image/jpeg" || $_FILES['fupload']['type'] == "image/pjpeg")
{ 
	$file_ext = strrchr($_FILES['fupload']['name'], '.');   // Get The File Extention In The Format Of , For Instance, .jpg, .gif or .php 
	$copy = copy($_FILES['fupload']['tmp_name'], "$idir" . $_FILES['fupload']['name']);   // Move Image From Temporary Location To Permanent Location 
}
}

/***********************Get Record***********************/
$trimmed = (!empty($_GET['ref']))?trim($_GET['ref']):"";

# connect to database
$cid = mysql_connect($host,$usr,$pwd);
if (!$cid){
echo("ERROR: " . mysql_error() . "\n");
}

$query = "SELECT * FROM spares where ref like \"%$trimmed%\" LIMIT 1";
$result = mysql_query($query);  
$record = mysql_fetch_assoc($result);
if(mysql_num_rows($result) > 0)
{
echo "FOUND";
}
$ref=$record["ref"];
$title=$record["title"];
$descr=$record["descr"];
$partno=$record["partno"];
$price=$record["price"];
$avail=$record["avail"];
$image=$record["image"];


/***********************Save Record***********************/
# this is processed when the form is submitted
# back on to this page (POST METHOD)
if (isset($_POST['submit'])){
$ref=$_POST["ref"];
$title=$_POST["title"];
$descr=$_POST["descr"];
$partno=$_POST["partno"];
$price=$_POST["price"];
$avail=$_POST["avail"];
$image=$_POST["image"];

# double-up apostrophes -- WHY ? use mysql_real_escape_string 
/*
$title = str_replace("'","''",$title);
$descr = str_replace("'","''",$descr);
$partno = str_replace("'","''",$partno);
$price = str_replace("'","''",$price);
$avail = str_replace("'","''",$avail);
*/

# setup SQL statement
$SQL = sprintf("UPDATE `spares` SET `title` = '%s',`descr` = '%s',`partno` = '%s',`price` = '%s',`avail` = '%s',`image` = '%s' WHERE `spares`.`ref` ='%s' LIMIT 1",
	mysql_real_escape_string($title),mysql_real_escape_string($descr),mysql_real_escape_string($partno),mysql_real_escape_string($price),mysql_real_escape_string($avail),mysql_real_escape_string($image),mysql_real_escape_string($ref));

#execute SQL statement
$result = mysql_db_query($db,"$SQL",$cid);
//$ID=mysql_insert_id(); //Pointless its an updatre not an insert!!

# check for error
if (!$result){
	echo("ERROR: " . mysql_error() . "\n$SQL\n");
}
echo "<P>Part updated</P>\n";
}
   
?>


<form enctype="multipart/form-data" action="<?php echo $_SERVER['PHP_SELF'] ?> " method ="post">
<div class="input_row">
<div class="input_titles">Title:</div>
<div class="input_box"><INPUT NAME="title" TYPE="text" id="title" VALUE="<?php echo $title; ?>" SIZE=50>
e.g. Avondale Dart Rooflight     </div>
</div>
<div class="input_row"><div class="input_titles">Description:<br /><br />(Use this space to add further descriptive text about this spare part e.g. dimensions etc.)</div><div class="input_box"><textarea name="descr" cols="50" rows="10" id="descr" type="text" value="<?php echo $descr; ?>"</textarea></div>
</div>
<div class="input_row"><div class="input_titles">Part number:</div><div class="input_box"><input name="partno" type="text" id="partno" value="<?php echo $partno; ?>" size="10" />
</div></div>
<div class="input_row"><div class="input_titles">Price (£) :</div><div class="input_box"><input name="price" type="text" id="price" value="<?php echo $price; ?>" size="15" />
(number only, no commas or pound signs)</div></div>
<div class="input_row"><div class="input_titles">In stock:</div><div class="input_box"><input name="avail" type="text" id="avail" value="<?php echo $avail; ?>" size="4" />
(number only)</div></div>
<input type = "hidden" name="MAX_FILE_SIZE" value = "102400">
<input type = "hidden" name="ref" value = "<?php echo $ref; ?>">
<div class="input_row"><div class="input_titles">Select image:</div><div class="input_box"><input type = "file" name = "fupload"></div></div>
<div class="input_row"><div class="input_titles"><input name="submit" type="submit" value="Update part" /></div></div>
</FORM>

joshgarrod · June 8, 2009

Hi, it doesn't show the record data in the fields. I am getting the following warnings:

Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in C:\Users\website\test.php on line 40

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in C:\Users\website\test.php on line 41

Thanks for taking the time

MadTechie · June 8, 2009

Few updates

Your need to update the select database line

<?php
error_reporting (E_ALL ^ E_NOTICE);
$idir = "uploads/";   // Path To Images Directory
$usr = "usr";
$pwd = "pass";
$db = "db";
$host = "srv";

/***********************Upload File***********************/
if (isset ($_FILES['fupload']))
{
//upload the image to tmp directory
$url = $_FILES['fupload']['name'];   // Set $url To Equal The Filename For Later Use 
if ($_FILES['fupload']['type'] == "image/jpg" || $_FILES['fupload']['type'] == "image/jpeg" || $_FILES['fupload']['type'] == "image/pjpeg")
{ 
	$file_ext = strrchr($_FILES['fupload']['name'], '.');   // Get The File Extention In The Format Of , For Instance, .jpg, .gif or .php 
	$copy = copy($_FILES['fupload']['tmp_name'], "$idir" . $_FILES['fupload']['name']);   // Move Image From Temporary Location To Permanent Location 
}
}

/***********************Get Record***********************/
$ref = (!empty($_GET['ref']))?trim($_GET['ref']):"";

# connect to database
$cid = mysql_connect($host,$usr,$pwd);
if (!$cid){
echo("ERROR: " . mysql_error() . "\n");
}
$db_selected = mysql_select_db('foo', $cid); //UPDATE

$query = sprintf("SELECT * FROM spares where ref like \"%%s%\" LIMIT 1",mysql_real_escape_string($ref));
$result = mysql_query($query) or die($query."<br>".mysql_error());  
$record = mysql_fetch_assoc($result);
if(mysql_num_rows($result) > 0)
{
echo "FOUND";
$ref=$record["ref"];
$title=$record["title"];
$descr=$record["descr"];
$partno=$record["partno"];
$price=$record["price"];
$avail=$record["avail"];
$image=$record["image"];
}else{
echo "NOT FOUND";
$ref=0;
$title="";
$descr="";
$partno="";
$price="";
$avail="";
$image="";
}


/***********************Save Record***********************/
# this is processed when the form is submitted
# back on to this page (POST METHOD)
if (isset($_POST['submit'])){
$ref=$_POST["ref"];
$title=$_POST["title"];
$descr=$_POST["descr"];
$partno=$_POST["partno"];
$price=$_POST["price"];
$avail=$_POST["avail"];
$image=$_POST["image"];

# setup SQL statement
$query = sprintf("UPDATE `spares` SET `title` = '%s',`descr` = '%s',`partno` = '%s',`price` = '%s',`avail` = '%s',`image` = '%s' WHERE `spares`.`ref` ='%s' LIMIT 1",	mysql_real_escape_string($title),mysql_real_escape_string($descr),mysql_real_escape_string($partno),mysql_real_escape_string($price),mysql_real_escape_string($avail),mysql_real_escape_string($image),mysql_real_escape_string($ref));

#execute SQL statement
$result = mysql_db_query($db,$query,$cid) or die($query."<br>".mysql_error());
//$ID=mysql_insert_id(); //Pointless its an updatre not an insert!!

# check for error
if (!$result){
	echo("ERROR: " . mysql_error() . "\n$SQL\n");
}
echo "<P>Part updated</P>\n";
}
   
?>


<form enctype="multipart/form-data" action="<?php echo $_SERVER['PHP_SELF'] ?> " method ="post">
<div class="input_row">
<div class="input_titles">Title:</div>
<div class="input_box"><INPUT NAME="title" TYPE="text" id="title" VALUE="<?php echo $title; ?>" SIZE=50>
e.g. Avondale Dart Rooflight     </div>
</div>
<div class="input_row"><div class="input_titles">Description:<br /><br />(Use this space to add further descriptive text about this spare part e.g. dimensions etc.)</div><div class="input_box"><textarea name="descr" cols="50" rows="10" id="descr" type="text" value="<?php echo $descr; ?>"</textarea></div>
</div>
<div class="input_row"><div class="input_titles">Part number:</div><div class="input_box"><input name="partno" type="text" id="partno" value="<?php echo $partno; ?>" size="10" />
</div></div>
<div class="input_row"><div class="input_titles">Price (£) :</div><div class="input_box"><input name="price" type="text" id="price" value="<?php echo $price; ?>" size="15" />
(number only, no commas or pound signs)</div></div>
<div class="input_row"><div class="input_titles">In stock:</div><div class="input_box"><input name="avail" type="text" id="avail" value="<?php echo $avail; ?>" size="4" />
(number only)</div></div>
<input type = "hidden" name="MAX_FILE_SIZE" value = "102400">
<input type = "hidden" name="ref" value = "<?php echo $ref; ?>">
<div class="input_row"><div class="input_titles">Select image:</div><div class="input_box"><input type = "file" name = "fupload"></div></div>
<div class="input_row"><div class="input_titles"><input name="submit" type="submit" value="Update part" /></div></div>
</FORM>

joshgarrod · June 8, 2009

Page now appears as follows:

SELECT * FROM spares where ref like "%s LIMIT 1
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '"%s LIMIT 1' at line 1

MadTechie · June 8, 2009

oops

update

$query = sprintf("SELECT * FROM spares where ref like \"%%s%\" LIMIT 1",mysql_real_escape_string($ref));

to

$query = sprintf("SELECT * FROM spares where ref like '%s' LIMIT 1","%".mysql_real_escape_string($ref)."%");

joshgarrod · June 8, 2009

Perfect! Thanks, that has solved most of it. Just that the record data now shows in the form fields except the description text field and the image field.

MadTechie · June 8, 2009

change

<div class="input_row"><div class="input_titles">Description:<br /><br />(Use this space to add further descriptive text about this spare part e.g. dimensions etc.)</div><div class="input_box"><textarea name="descr" cols="50" rows="10" id="descr" type="text" value="<?php echo $descr; ?>"</textarea></div>

to

<div class="input_row"><div class="input_titles">Description:<br /><br />(Use this space to add further descriptive text about this spare part e.g. dimensions etc.)</div><div class="input_box"><textarea name="descr" cols="50" rows="10" id="descr" type="text" ><?php echo $descr; ?></textarea></div>

as for the image.. try this

<div class="input_row"><div class="input_titles">Select image:</div><div class="input_box"><input type = "file" name = "fupload"></div></div>

to

<?php
if(empty($image))
{
?>
<div class="input_row"><div class="input_titles">Select image:</div><div class="input_box"><input type = "file" name = "fupload"></div></div>
<?php
}else{
echo '<img src="'.$idir.$image.'">";
}

joshgarrod · June 8, 2009

Genius, thank you so much, all working as should. Much appreciated

MadTechie · June 8, 2009

your very welcome

Sign In

[SOLVED] Updating database records

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

Link to comment

Share on other sites

Archived

Important Information