macOS PHP database update question

[barr08]

Hi, I am working on a project for a MySQL class, and I am creating a sample database in which I need to create a table, add info to this table (name, email, etc.), view the table, and update fields that already exist on the table, using PHP. I have the first three working, but I am having trouble updating fields. I want to be able to enter a name in an html form, and enter information in text boxes to replace the existing information in the database.

My files are located here:
http://nemo.mwd.hartford.edu/~bbarr/IIT310/Final/FinalPHPDB/

This one creates the field:
http://nemo.mwd.hartford.edu/~bbarr/IIT310/Final/FinalPHPDB/dataEntry.html

This views the info:
http://nemo.mwd.hartford.edu/~bbarr/IIT310/Final/FinalPHPDB/retrieveDisplayAll.php

I want to be able to edit the four fields. Any help or input would be great. I may have explained this in a confusing way, so I'll try and clarify if you have any questions. I am not much of a programmer, I focus on the design aspect of my web design major, but I figured this stuff would be good to have at least a working knowledge of.

Thanks for looking!

 

[antibact1]

Assuming that you just want to have them enter a name and details, if the name does not exist then add all details, otherwise, find the name and replace the details.

$query = "select * from table where name = $name";
$result = mysql_query($query);

if (mysql_num_rows($result) == 0)
{

/* New add to the system */
$insert_query = "insert into ....";​

}
else
{

/* Update */
$row = mysql_fetch_assoc($result);

/* Get the old ID */
$id = $row["id"];

/* Create the update query */
$update_query = "update table set ...... where id = $id";​

}

This also assumes the name is unique. Otherwise, you will potentially have multiple rows to update.

 

[barr08]

Thanks, i'll try this out, it might be easier to edit the fields based on a unique account number (000x) i have asigned to each account, that way I know I am editing a unique field.

 

[antibact1]

Thanks, i'll try this out, it might be easier to edit the fields based on a unique account number (000x) i have asigned to each account, that way I know I am editing a unique field.

If you want to do it that way, in your item listing page, add an edit link that supplies the item ID through GET. Then, when you are displaying the item to the user in a form allowing them to edit the details, add a hidden field containing the id so you will have it in POST.

A link will be in the form http://site.com/edit_item&id=5

if (isset($_GET["id"]))
{

/* Retrieve the item from the database and prepopulate the form with the existing values */
$id = $_GET["id"];
$query = "select * from table where id = $id";

$row = mysql_fetch_assoc($row);

/* Display the form with the hidden ID field */​

}
else if (isset($_POST["id"]))
{

/* Retrieve the new details from the form and update the database entry */
$id = $_POST["id"];

$query = "update table .... where id = $id";​

}

Note that this contains no error checking (ie. an invalid ID can be supplied). Also, it may be useful to check if the values being updated are in fact changed.

 

[barr08]

OK I have tried the first solution posted by antibact1, and I think I am doing something wrong. I have it up at: http://nemo.mwd.hartford.edu/~bbarr/IIT310/Final/FinalPHPDB/
under updateData.php. My code is basically what antibact1 gave me.

$hookup=mysql_connect($server, $user, $pass);

$dbNow=@mysql_select_db($currentDB, $hookup);

$query = "select * from $currentTable where uname = $uname";
$result = mysql_query($query);

if (mysql_num_rows($result) == 0)
{

/* New add to the system */
$insert_query = "insert into $currentTable";

}
else
{

/* Update */
$row = mysql_fetch_assoc($result);

/* Get the old ID */
$id = $row["id"];

/* Create the update query */
$update_query = "update $currentTable set ...... where id = $id";

}

Any ideas? I know the "....." right above has to be something, but I am not sure what. I am getting an error on the 15th line, "
if (mysql_num_rows($result) == 0)"

Thanks

 

[antibact1]

Sorry the code I gave was just a template.

There are several other things you do. Here would be a full example with no error checking.

$hookup=mysql_connect($server, $user, $pass);

$dbNow=@mysql_select_db($currentDB, $hookup);

/* Pull all of the form variables and set them */
$uname = $_POST["uname"];
$email = $_POST["email"];
$acnt = $_POST["acnt"];
$cost = $_POST["cost"];

$query = "select * from $currentTable where uname = $uname";
$result = mysql_query($query);

if (mysql_num_rows($result) == 0)
{

/* New add to the system */
$insert_query = "insert into $currentTable (uname, email, acnt, cost) values ($uname, $email, $acnt, $cost)";

$result = mysql_query($insert_query);​

}
else
{

/* Update */
$row = mysql_fetch_assoc($result);

/* Get the old ID */
$id = $row["id"];

/* Create the update query */
$update_query = "update $currentTable set uname = $uname, email = $email, acnt = $acnt, cost = $cost where id = $id";

$result = mysql_query($update_query);​

}

Note that none of the queries escape any special characters that may mess up your query. It also assumes that each of the parameters has been set. Ideally, you would check each of the parameters to determine that they have been set, and construct your queries that way.

If you need more help, just let me know.

 

[barr08]

Oh it was a template. I feel so stupid...

I'm going to give this a shot, thanks for your help.

 

[barr08]

OK so I am still getting an error on the rows line. The error is:

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/MWD/bbarr/www/IIT310/Final/FinalPHPDB/updateData.php on line 21

I recognize this error from other programs, i think it is pretty broad. Do you have any idea what it could mean in this code?

 

[antibact1]

No worries.

That error means the query is not executing successfully. So when you pass the result resource to mysql_num_rows, it is not a valid result. Is the variable $currentTable defined prior to executing any of that code. Another suggestion is to execute

echo $query;

after the query to take a look at the query. Also, you may want to do a

echo mysql_error();

to get a better idea of what the error is.

 

[macfaninpdx]

You are referencing a variable, $currentTable, that is not defined anywhere. So PHP will use an empty value in it's place, making the $query variable look something like:
"select * from where uname = <some_user>"

If the mysql table you are selecting from will not change, you can safely replace the $currentTable variable with the name of the table. If it needs to be dynamic, then make sure to set the $currentTable variable prior to using it in the query statement.

In the case where the table name won't change, the line setting the query variable would look like this:

$query = "select * from <table_name> where uname = '$uname'";

And you would of course replace <table_name> above with the actual name of your table. Make sense?

Oh, and one more potential gotcha. You will need to add single-quotes around each variable where text will be used in the query. So for example

$insert_query = "insert into <table_name> (uname, email, acnt, cost) values ('$uname', '$email', '$acnt', '$cost')";

and

$update_query = "update <table_name> set uname = '$uname', email = '$email', acnt = '$acnt', cost = '$cost' where id = '$id'";

Technically the $id above deosn't need to be wrapped in single-quotes, but it won't hurt. I hope this helps. Let us know if you have any more questions.

 

[barr08]

You are referencing a variable, $currentTable, that is not defined anywhere. So PHP will use an empty value in it's place, making the $query variable look something like:
"select * from where uname = <some_user>"

If the mysql table you are selecting from will not change, you can safely replace the $currentTable variable with the name of the table. If it needs to be dynamic, then make sure to set the $currentTable variable prior to using it in the query statement.

In the case where the table name won't change, the line setting the query variable would look like this:

$query = "select * from <table_name> where uname = '$uname'";

And you would of course replace <table_name> above with the actual name of your table. Make sense?

I have $currentTable defined in the program, it is above the section of the code that I put in this thread. Is this something I still need to do? I am still getting the error on the mysql_num_rows() line.

 

[macfaninpdx]

I am still getting the error on the mysql_num_rows() line.

Put the single-quotes around the $uname variabe, as mentioned above. And you might as well put the single-quotes around the rest of the variables in your mysql queries, because they will give you errors as well.

 

[barr08]

Put the single-quotes around the $uname variabe, as mentioned above. And you might as well put the single-quotes around the rest of the variables in your mysql queries, because they will give you errors as well.

OK so I did this and it is still happening ! Any other ideas? My code now looks like this:

<?php
	$server="localhost";
	$user="bbarr";
	$pass="******";
	$currentDB="bbarr";
	$currentTable="boyfight3";

$hookup=mysql_connect($server, $user, $pass);

$dbNow=@mysql_select_db($currentDB, $hookup);

/* Pull all of the form variables and set them */
$uname = $_POST["uname"];
$email = $_POST["email"];
$acnt = $_POST["acnt"];
$cost = $_POST["cost"];

$query = "select * from $currentTable where uname = '$uname'";
$result = mysql_query($query);

if (mysql_num_rows($result) == 0)
{

    /* New add to the system */
    $insert_query = "insert into $currentTable (uname, email, acnt, cost) values ('$uname', '$email', '$acnt', '$cost')";

    $result = mysql_query($insert_query);

}
else
{

    /* Update */
    $row = mysql_fetch_assoc($result);

    /* Get the old ID */
    $id = $row["id"];

    /* Create the update query */
    $update_query = "update $currentTable set uname = '$uname', email = '$email', acnt = '$acnt', cost = '$cost' where id = '$id'";

    $result = mysql_query($update_query);

}

	
?>

 

[macfaninpdx]

OK so I did this and it is still happening ! Any other ideas? My code now looks like this:

First of all, it would be best if you remove your user name and password from your code above. Second, add the following line right in between the $query= and the $result= lines:

echo $query;

And then add the following after the $result= line:

echo mysql_error();

Let us know what that displays.

 

[barr08]

Haha shoot I forgot about the PW. I am so frazzled this is due very soon.

I added the echos, and I got:

select * from boyfight3 where uname = ''select * from boyfight3 where uname = ''
Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in /home/MWD/bbarr/www/IIT310/Final/FinalPHPDB/updateData.php on line 23

boyfight3 is the name of my table

 

[macfaninpdx]

I added the echos, and I got:

select * from boyfight3 where uname = ''select * from boyfight3 where uname = ''

This confuses me. According to this, the variable $uname is set to "select * from boyfight3 where uname=" which may be your problem. to debug, let's add this to the first line of your code (after the <?php):

print_r($_POST);
exit();

In order to view the formatted results, view the source of the returned web page and copy the results there.

 

[barr08]

The result is:

Array
(
)

?? lol

thanks for all your help by the way, very clutch!

 

[macfaninpdx]

OK, I understand the previous results now, too. Your $_POST is not getting populated by the referring page. Are you trying to load this page directly, or by posting a form from a referring page? Because only the latter will give you results that work. Otherwise, as you are seeing now, the $_POST variable (which is the global variable that holds all form POST information) is not getting populated.

To test the pade by loading directly (instead of using a form submit), enter something like this into the URL:
http://localhost/yourpage.php?uname=somename&email=someemail&acnt=someacnt&cost=somecost(blue text is the part to add)

In other words, you are injecting the variables manually into the URL since they are not being passed as POST variables. Also, remove the print_r and exit() lines previously added.

 

[barr08]

OK I removed the two most recent lines, and added the blue text. I am still getting the same error:

select * from boyfight3 where uname = ''select * from boyfight3 where uname = ''

this is the URL I used:

http://nemo.mwd.hartford.edu/~bbarr...mename&email=someemail&acnt=cnt&cost=somecost

I have an HTML form for the page, but that returns the same error. I am also not sure if that form is correct.

 

[macfaninpdx]

Let's try adding the print_r($_POST) back in, with the updated URL.

 

[barr08]

Same error.

Array()

 

[RupertJ]

If you pass the variables in the URL like that, they end up in $_GET, not $_POST.

You can use a function like:

function getParam($name) {
if(isset($_GET[$name])) return $_GET[$name];
if(isset($_POST[$name])) return $_POST[$name];
return false;
}

If you want to switch between the two.

 

[macfaninpdx]

Try changing it to print_r($_REQUEST)

 

[barr08]

After changing it to REQUEST, I got this:

Array
(
    [uname] => somename
    [email] => someemail
    [acnt] => someacnt
    [cost] => somecost
    [__utmz] => 126326804.1183574340.1.1.utmccn=(referral)|utmcsr=hartford.facebook.com|utmcct=/profile.php|utmcmd=referral
    [__utma] => 126326804.1276275613.1183574340.1183574340.1184015861.2
    [WRUID] => 0
    [__utmc] => 126326804
)

 

[macfaninpdx]

If you pass the variables in the URL like that, they end up in $_GET, not $_POST.

Right, I forgot. Thanks. They will still show in $_REQUEST though.