how to use an output from one query in another

Icewolf · November 10, 2013

Hi

I need to pull a value from the database when a user enters in a code and add it to another table. The select query is working I have it displayed on the screen. It even says that the database has been updated. However when I go and look in the database the value has not been updated. I think it has to do with the $post but I am not sure.

Thanks

<!DOCTYPE html>
<?php

include 'connect.php';
include 'header_signin.php';
include 'timeout.php';

if($_SERVER['REQUEST_METHOD'] != 'POST')
{
	//someone is calling the file directly, which we don't want
	echo 'This file cannot be called directly.';
}
else
{
	//check for sign in status
	if(!$_SESSION['signed_in'])
	{
		echo 'You must be signed in to post a reply.';
	}
	else
	{
	
	$sql = " Select * from redeemer
		 where redeem_code = '".$_POST['redeem_code']."'";
		 $result = mysql_query($sql);
	
	
	if(!$result)
		{
			echo 'Redeemer code is not valid, please try again.';
		}
		else 
   {
     echo '<table border="1">
			  <tr>
				<th>Code</th>
				<th>points</th>
				
			  </tr>';	
			

while($row = mysql_fetch_assoc($result))
{
        echo '<tr>';
			echo '<td>' . $row['redeem_code'] . '</td>';
			echo '<td>'. $row['redeem_points']. '</td>';
			
		 echo '</tr>';
        
			$_SESSION['redeem_points'] 	= $row['redeem_points'];
}
		
		//a real user posted a real reply
          
		  		  $sql = sprintf("UPDATE `rewards` 
                        SET `point_earn`= `point_earn` + '". mysql_real_escape_string($_POST['redeem_points'])."' 
                        WHERE member = '" . mysql_real_escape_string($_SESSION['user_name']) . "' AND cat_name = 'bank'");
						
		$result = mysql_query($sql);
						
		if(!$result)
		{
			echo 'Redeemer code is not valid, please try again.';
		}
		else
		{
			echo 'Bank Points have been updated.';
	    }
      }
	}
}

include 'footer.php';
?>

Barand · November 10, 2013

Add some debugging code after the update query.

if(!$result)
        {
            echo 'Redeemer code is not valid, please try again.';
            echo '<br>' . $sql . '<br>' . mysql_error();
        }

mac_gyver · November 10, 2013

the logic you are using in this code (and your other current thread) is not doing exactly what you think.

the $result variable you are assigning from the msyql_query() statement will only be a false value if the query fails due to an error of some kind, i.e. the query does not run at all due to things like a connection problem, a syntax error in the query statement, wrong table/column names,...

a SELECT query or an UPDATE can run, but not match any rows, if the WHERE clause is false. this is a successful query and the $result variable won't be a false value.

so, your logic testing if $result is a false value and outputting messages like 'Redeemer code is not valid' are misleading.

for a SELECT query, you would need to test the mysql_num_rows($result) value and for an UPDATE query, you would need to test the mysql_affected_rows() value to know if the query did or didn't match or updated a row.

Edited November 10, 2013 by mac_gyver

Icewolf · November 10, 2013

Thanks Mac_gyer yeah I am realizing that now. How would I use mysql_affected_rows()? I know the select query is working because I can see the value on the screen. I just need to figure out what the update query is doing?

mac_gyver · November 10, 2013

for your UPDATE query, you need to troubleshoot and find out why it is not updating the row.

echo the $sql variable to make sure the query statement contains the values you expect and then look directly in your database table using your favorite database management tool and make sure you have a row that exactly matches what the query's WHERE clause is.

Icewolf · November 10, 2013

Thank you. I see what the issue is. it is that it is not seeing the variable being passed.

Here is what I am getting when I use echo '<br>' . $sql . '<br>' . mysql_error()

Bank Points have been updated.
UPDATE `rewards` SET `point_earn`= `point_earn` + '' WHERE member = 'testuser' AND cat_name = 'bank'

Barand · November 10, 2013

Add some debugging code after the update query.

if(!$result)
        {
            echo 'Redeemer code is not valid, please try again.';
            echo '<br>' . $sql . '<br>' . mysql_error();
        }

Told you that earlier in reply #2!

Icewolf · November 10, 2013

Barand I did use your code this is the results of putting that in the code.

mac_gyver · November 10, 2013

are you sure you want to use $_POST['redeem_points'] as the value to add in the UPDATE query, especially since you just retrieved $row['redeem_points'] from the first query in this code. also, by using an external $_POST value, you would allow someone to update their point_earn value to any thing they want.

Icewolf · November 10, 2013

No I am not sure I have been just googling on how to do these kind of things. Is there a different way of doing this?

mac_gyver · November 11, 2013

the question actually is - what did you define for where you wanted the value to come from?

programming is all about defining what you want, then writing the code that does it.

there must be some reason that you ran the SELECT query and retrieved the $row['redeem_points'] value out of the redeemer table based on the submitted $_POST['redeem_code'] value, otherwise you wouldn't have went to the trouble of putting those lines of code in your program. i'm guessing it was so that if the $_POST['redeem_code'] value was found that you would add the corresponding $row['redeem_points'] to the correct row in the rewards table?

Icewolf · November 11, 2013

Yes what I am trying to do is if a user enters a code it takes the reward that is associated with that code and adds it to their exciting rewards.

Icewolf · November 11, 2013

so in this case I will need to declare a local variable.

Icewolf · November 13, 2013

I guess I am not understanding what I am missing. Because isn't this creating a local variable

$rp = $row['redeem_points'];

Shouldn't I be able to use this later on?

UPDATE `rewards` 
                       SET `point_earn`= `point_earn` +  $_POST['rp']
                        WHERE member = '" . mysql_real_escape_string($_SESSION['user_name']) . "' 
						AND cat_name = 'bank'");

From what I can tell it is not recognizing it. From what I can tell it is not bringing over the value from the select query.

mac_gyver · November 13, 2013

how is the variable $rp the same as the variable $_POST['rp']?

this seems to be a copy/paste coding failure.

Edited November 13, 2013 by mac_gyver

Icewolf · November 13, 2013

It probably is I was using code that I know has worked in other places. I just assumd that $_post was correct but I am taking that now as when a user post a value some where is when you use that.

Icewolf · November 13, 2013

Thank you mac_gyve I was able to get it.

Sign In

how to use an output from one query in another

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

Join the conversation

Important Information