Help grabbing variables from mySQL column with PHP

[dougevans]

Hello,
I am a complete beginner with this and am hoping you can help.
I am accessing a pre-made mySQL database with a query and trying to get to the information it is getting.

Here is my code:

<?php

$subscriberid=$_GET['subscriberid'];
$dbhost = 'localhost';
$username='myusername';
$password='mypassword';
$database='mydb';

$conn = mysql_connect($dbhost,$username,$password) or die ('Error connecting to mysql');
echo "Connected to MySQL<br />";

mysql_select_db($database) or die ('Unable to select database');
echo "Connected to database em<br />";

$result = mysql_query("SELECT * FROM email_subscribers_data WHERE subscriberid='$subscriberid'")
or die(mysql_error());

$row = mysql_fetch_array( $result );
// Print out the contents of the entry

echo " First Name: ".$row['?'];
echo " Last Name: ".$row['?'];
echo " Phone: ".$row['?'];
echo " City: ".$row['?'];
echo " State: ".$row['?'];
echo " ZIP Code: ".$row['?'];

?>


My question is what goes in the place of the ?'s above?

The structure is like this:

subscriberid | fieldid | data
21 | 1 | Bill (First Name)
21 | 2 | Smith (Last Name)
21 | 3 | 555-555-5555 (Phone)
21 | 4 | Los Angeles (City)
21 | 5 | California (State)
21 | 6 | 92066 (ZIP Code)

I didn't set the database up but I would like to access it somehow. I've been trying to educate myself for a couple of hours now but cannot find an answer. Any help would be appreciated.

Thanks in advance,
Doug

[TheMadScientist]

Welcome to WebmasterWorld!
This should give you an idea:

while($row = mysql_fetch_array( $result )) {
// Print out the contents of the entry

 if($row['fieldid']==1) {
 echo " First Name: ".$row['data'];
 }
 elseif($row['fieldid']==2) {
 echo " Last Name: ".$row['data'];
 }
 elseif($row['fieldid']==3) {
 echo " Phone: ".$row['data'];
 }
 elseif($row['fieldid']==4) {
 echo " City: ".$row['data'];
 }
 elseif($row['fieldid']==5) {
 echo " State: ".$row['data'];
 }
 elseif($row['fieldid']==6) {
 echo " ZIP Code: ".$row['data'];
 }
}

Also, make sure you 'scrub' any user input before doing anything with it, especially connecting to a database...
There are quite a few threads here about validating form input where you can get some better ideas but at the very least you should:

$dbhost = 'localhost';
$username='myusername';
$password='mypassword';
$database='mydb';

$conn = mysql_connect($dbhost,$username,$password) or die ('Error connecting to mysql');
echo "Connected to MySQL<br />";

mysql_select_db($database) or die ('Unable to select database');
echo "Connected to database em<br />";

$subscriberid=[i]mysql_real_escape_string[/i]($_GET['subscriberid']);

Too late for me to expand too much, but someone else will probably come along and add a bit or two of information.

If you want to find some more information on user input, try using the search here and looking for 'sanitize user input', 'validating form input' or something along those lines.

[dougevans]

Awesome! Thank you so much! Can you help again please? This time I am starting with different query and trying to pass a variable to a second query. Here is the code I have so far:

<?php

$emailaddress=$_GET['emailaddress'];
$dbhost = 'localhost';
$username='myusername';
$password='mypassword';
$database='mydb';


$conn = mysql_connect($dbhost,$username,$password) or die ('Error connecting to mysql');
echo "Connected to MySQL<br />";

mysql_select_db($database) or die ('Unable to select database');
echo "Connected to database<br /><br />";

$result = mysql_query("SELECT * FROM email_list_subscribers WHERE emailaddress='$emailaddress'")
or die(mysql_error());

$row = mysql_fetch_array( $result );
// Print out the contents of the entry

echo "Email: ".$row['emailaddress'];
echo "<br />";
echo "Subscriber ID: ".$row['subscriberid'];
echo "<br />";

//need to pass subscriberid variable here:

$result = mysql_query("SELECT * FROM email_subscribers_data WHERE subscriberid='$subscriberid'")
or die(mysql_error());

while($row = mysql_fetch_array( $result )) {
// Print out the contents of the entry

if($row['fieldid']==2) {
echo " First Name: ".$row['data'];
echo "<br />";
}
elseif($row['fieldid']==3) {
echo " Last Name: ".$row['data'];
echo "<br />";
}
elseif($row['fieldid']==4) {
echo " Phone: ".$row['data'];
echo "<br />";
}
etc. etc....

The first part is working but I am having trouble passing the variable to the second query.

After this is working, I will definitely be looking into scrubbing the input.

Any direction would be GREATLY appreciated,
Doug

[Readie]

$result = mysql_query("SELECT * FROM email_subscribers_data WHERE subscriberid='$subscriberid'") or die(mysql_error());

Calling the wrong variable here, $subscriberid is not set. $row['subscriberid'] is what you want:

$sql = 'SELECT * FROM email_subscribers_data WHERE subscriberid = "' . $row['subscriberid'] . '"';
$result = mysql_query($sql) or die(mysql_error()); 

[dougevans]

Thank you Readie. Just what I needed!

Doug

[dougevans]

Hello everyone. Now I am trying to auto increment a field through PHP. This is what I have:

elseif($row['fieldid']==17) {
$visited1 = $row['data'];
echo "&visited1=$visited1";
mysql_query "UPDATE $row['data'] SET 'data' = 'data' + 1";
}

Is this something that has to be done outside of the elseif statement? It seems appropriate to go here?

Any help or direction would be great!

[rocknbil]

Why can't you set the field to autoincrement and let it do it automatically?

alter table yourtable change yourfield yourfield int(11) auto_increment;

Then you never even have to insert anything in that field, any record you insert will increment that field automatically.

Barring that, you don't quote field names, you almost have it:

$query = "UPDATE table SET data=data+1";
mysql_query($query);

oops, that will increment all records, if that's not what you want,

$query = "UPDATE table SET data=data+1 where id=1234";
mysql_query($query);

Be sure it's got a default value though, if you allow null in data it won't increment. null+1 is still null. :-)

[dougevans]

Thanks roknbill! That did it.

[dougevans]

Hey! You guys have been a great help. Can I ask again please?
I am now trying to update the same mySQL database with an update.php file but I am trying to update multiple rows at once and am not sure I have the syntax correct as it is not working. Here is what I have:

<?php
$mySubscriberID=$_POST['mySubscriberID'];
$phone=$_POST['phone'];//fieldid=4
$myCity=$_POST['myCity'];//fieldid=8
$myState=$_POST['myState'];//fieldid=9
$zipCode=$_POST['zipCode'];//fieldid=10
$address1=$_POST['address1'];//fieldid=14

$dbhost='localhost';
$username='myusername';
$password='mypass';
$database='mydb';

$conn = mysql_connect($dbhost,$username,$password) or die ('Error connecting to mysql');

//begin updates
$query = '
UPDATE email_subscribers_data SET data=$phone WHERE subscriberid = $mySubscriberID AND fieldid=4
UPDATE email_subscribers_data SET data=$myCity WHERE subscriberid = $mySubscriberID AND fieldid=8
UPDATE email_subscribers_data SET data=$myState WHERE subscriberid = $mySubscriberID AND fieldid=9
UPDATE email_subscribers_data SET data=$zipCode WHERE subscriberid = $mySubscriberID AND fieldid=10
UPDATE email_subscribers_data SET data=$address1 WHERE subscriberid = $mySubscriberID AND fieldid=14
';
mysql_query($query); 

echo "&myMessage=All Records Have Been Updated";

?> 

Also, I am noticing that what is being posted to this file is in HTML format, I.E. "&address1=1234%20Big%20Oak%20St%2E". Is it necessary to change this somehow so that the mySQL database will understand it?

Thanks in advance for any help!
Doug

[Readie]

That fieldid stuff is confusing me a bit - I don't see why you don't just have several columns. At any rate it's stopping you from doing all this with a single statement. Could do it like this though:

[pre]$base = 'UPDATE email_subscribers_data SET data = ';
$query = array();
$query[] = '"' . $phone . '" WHERE subscriberid = "' . $mySubscriberID . '" AND fieldid = 4';
$query[] = '"' . $myCity . '" WHERE subscriberid = "' . $mySubscriberID . '" AND fieldid = 8';
$query[] = '"' . $myState . '" WHERE subscriberid = "' . $mySubscriberID . '" AND fieldid = 9';
$query[] = '"' . $zipCode . '" WHERE subscriberid = "' . $mySubscriberID . '" AND fieldid = 10';
$query[] = '"' . $address1 . '" WHERE subscriberid = "' . $mySubscriberID . '" AND fieldid = 14';

$count = count($query);
for($i = 0; $i < $count; $i++) {
  $sql = $base . $query[$i];
  mysql_query($sql);
}[/pre]

[dougevans]

Hi Readie. Thanks for your reply..

Yes, as a beginner, the fieldid is confusing the hell out of me. Unfortunately, it is because I am working with an existing database and that is the way they have set it up.

I tried your example but it did not appear to work. Could it be because of the formatting of the post mentioned above?(&address1=1234%20Big%20Oak%20St%2E)

Also, does the:

AND fieldid = 4 maybe need to be:
AND fieldid == 4 with two = signs? Or am I confusing this with actionscript from Flash?

Thanks again,
Doug

[Readie]

In SQL "=" means "is equal to" - whereas PHP, JavaScript (And I assume Flash?) requires two to mean "is equal to".

I edited my post a few times too, a few syntax errors - so make sure you got my latest, because I'm pretty sure that the SQL there now is good.

[rocknbil]

Three things:

I am noticing that what is being posted to this file is in HTML format, I.E. "&address1=1234%20Big%20Oak%20St%2E".

This tells me you are either missing the method from your form,

<form action="script.php">

or you're using get,

<form action="script.php" method="get">

which produces the encoded query string you see. If no method is specified, it will default to get.

Yet you're attempting to read values from $_POST. Make sure you are using the post method:

<form method="post" action="script.php">

Second, while numeric values in select statements do not need quoting, "everything else" does (see Readie's post.) Another way to look at it . . .

$query = " UPDATE email_subscribers_data SET data='" . $phone . "' WHERE subscriberid = $mySubscriberID AND fieldid=4";

Third: Readie's idea loops through and makes multiple mysql queries, and what it says is your original has several queries but are only executing the query ONCE, at the end. So even if it worked, it would only work for the last one. An alternative could be, though less graceful, to add a semicolon at the end of each update statement, then multiple mysql_query() statements between.

update statement . . .;
mysql_query()....
update statement . . .;
mysql_query()....
update statement . . .;
mysql_query()....

Though you can do that sort of thing with comma separated values for insert,

insert into table (fieldname) values ('value1'), ('value2'), ('value3');

you can't do it with an update statement because each update needs a where clause. (You may be able to, but I'm not aware of it.)

[dougevans]

Hey Everyone. Thank you for the replies, you guys are awesome. I think I need to back peddle a little here as I am still stuck at the:

am noticing that what is being posted to this file is in HTML format, I.E. "&address1=1234%20Big%20Oak%20St%2E".

I am actually using Flash to access and update the database, using a command called "sendAndLoad" which POSTs to the php file and then loads the response or echoed variables back into Flash. This has been working very well up to this point. Therefore the encoded query string is coming from Flash and not because I am using GET or POST. This is an issue I will need to address in Flash I'm afraid and probably not appropriate for you guys or this forum.

BUT -- the database is still not being affected. Wouldn't the database still be updated with the "1234%20Big%20Oak%20St%2E" text string, regardless of the funny characters? Rather than tackle the multiple updates all at once, I am now attempting to update just one of the fields and work from there. The code I have is:

$mySubscriberID=$_POST['mySubscriberID'];//this is passing correctly
$phone=$_POST['phone'];//fieldid=4
$query = 'UPDATE email_subscribers_data SET data=$phone WHERE subscriberid = $mySubscriberID AND fieldid=4';
mysql_query($query);

Do you guys see any problem with this and should'nt it work? Any feedback is very much appreciated and once I work past this issue, I'm sure your feedback above will be very useful!

Thanks again!
Doug

[Readie]

Does doing the following return any error messages?:

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

(Replace the existing mysql_query inside the for() loop)

[rocknbil]

sing a command called "sendAndLoad" which POSTs to the php file and then loads the response or echoed variables back into Flash.

"Curiouser and curiouser" cried Alice . . . . :-) sendAndLoad is an XML method, and it matters whether you're using straight sendAndLoad or the sendAndLoad(LoadVars) method.

public sendAndLoad(url:String, resultXML:XML) : Void

public sendAndLoad(url:String, target:Object, [method:String]) : Boolean

This first will send only, and expect, an XML string, the second can be set to post but is still an XML method. I guess it's irrelevant, since it was working at some point (right?) In the spirit of "right tool for the job," I'd probably use getURL or loadVars.

getURL('yourscript.php",'post');

More simply: what happens if you change all your script reads to $_GET?

$mySubscriberID=$_GET['mySubscriberID'];
$phone=$_GET['phone'];//... etc

Wouldn't the database still be updated with the "1234%20Big%20Oak%20St%2E" text string, regardless of the funny characters?

I think you may be correct, this is a combination of what Flash is sending and what PHP is receiving, we would need to see both in action to tell what's really "going on."

[dougevans]

Hey Readie and rocknbil. Well, I stripped everything down to where I placed the variables into the PHP script itself and I found some minor (major) syntax errors that kept the whole thing from working. Biting too much off at once I suppose. Anyways, little by little, I got the script to update the db by itself and then incorporated the query array that Readie suggested and now the whole thing is working great! The PHP is passing the variables to the db without the special characters as well, even though they are still in the post. Thank you both for the support and patience...

rocknbil: I tried replacing the $_POST's to $_GET's but got this error:

Notice: Undefined index: phone in C:\wamp\www\update.php on line 10

I guess the sendAndLoad was taken from XML by Flash, probably because of the similarities?

So, my next step is to send out an email after the db is updated. Wish me luck! (Any advice?)

Thanks again,
Doug