query two databases and return, and set values

[jeagerjr]

I have one database with two tables.

 

The first table (userinfo) works fine. That table is for registration.

 

The second part of the code is to send a value (linkcode) to the second table and return another value from the same line.

 

Here are the fields in the table. The HTML page has the user enter the linkcode.

 

Sample Record

linkcode="12345", satisfied="no", linkid="", prize="cool prize"

 

I want to send the linkcode to the table, check to see if "satisfied" field is "no", if it is no, it changes to "yes", and return the "prize" field to display on screen.

 

If the "linkcode" doesn't exist or if the "satisfied" field is yes, then it returns errors.

 

I have no idea how to proceed. Any help would be so appreciated.

 

Here is the code. The first part works. My question starts at "//my attempt at…"

 

Thanks again in advance.

 

je

 

Here is my code:

<?php
$con = mysql_connect("hostname","username","password"); 
if (!$con) 
{ 
die('Could not connect: ' . mysql_error()); 
} 
mysql_select_db("database_name", $con); 
$first_name=mysql_real_escape_string($_POST['first_name']); 
$last_name=mysql_real_escape_string($_POST['last_name']); 
$email=mysql_real_escape_string($_POST['email']);
$phone=mysql_real_escape_string($_POST['phone']);
$street_address=mysql_real_escape_string($_POST['street_address']);
$city=mysql_real_escape_string($_POST['city']);
$sql="INSERT INTO userinfo (first_name,last_name,email,phone,street_address,city,state,zip) VALUES ('$first_name','$last_name','$email','$phone','$street_address','$city','$state','$zip')"; 
if (!mysql_query($sql,$con)) {
die('Error: ' . mysql_error()); 
} 

//my attempt at communicating with the database
$link = mysql_connect("hostname","username","password");
if (!$link) {
    die('Could not connect: ' . mysql_error());
}
if (!mysql_select_db('database_name')) {
    die('Could not select database: ' . mysql_error());
}
$result = mysql_query('?????????????');
if (!$result) {
    die('Could not query:' . mysql_error());
}
echo mysql_result($result, 2);

mysql_close($link);

//end of my attempt

mysql_close($con);
?>

 

[Soldier Jane]

It seems you are asking for the syntax to retrieve data from your database. Before that though, why are you using 2 databases? This could easily be achieved using another table in the same database. Anyway, try using something along the lines of:

$sql = "SELECT * FROM prizes WHERE linkcode='$linkcode' AND satisfied='no'";
$query = mysql_query($sql)
if(!query)
   {
      echo 'Code expired/prize redeemed.';
   }else{
      $result = mysql_fetch_array($query);
      echo 'Congratulations, you won ' . $result['prize'] . '!';
      $sql = "UPDATE prizes SET satisfied='yes' WHERE linkcode='$linkcode'";
      $query = mysql_query($sql);
   }

 

 

 

I think that should be right. Obviously you need to change the table name and the code inside the if statement. You may want to change the SELECT statement to only grab a particular field. Here I took all the data, whereas I only output the prize.

[jeagerjr]

Thank you, Soldier Jane. I'm kinda excited about trying that.

 

You mentioned two databases. I thought I only had one database. I think you may be talking about opening the same database twice. I'll kill that line.

 

Thanks for the help. I'll post if I have more questions.

 

 

[Soldier Jane]

Well I thought you had two databases because of the topic:

 

query two databases and return, and set values

 

And because yes, you were opening the connection twice, which is unnecessary. Don't forget to remove the second mysql_close() either.

 

Glad to help.