[RESOLVED] insert into one table then update another table

etoast

Could someone please take a look at the below code and suggest why the second update part isn't working. I don't get any errors showing, but only the $sql and $sql2 work. $sql3 and $sql4 don't work. I'm sure you'll be horrified at the code as a whole, sorry. I'm not very good, but I am trying 🙂

Thank you.

<?php
session_start();
require_once ('mysql_connect.php');
$id=$_GET['id']; 
$uname=$_SESSION['user_name'];

$sql2="SELECT * FROM table1 WHERE type='$id' AND uname='$uname'";
$mysql_result=mysql_query($sql2);
$num_rows=mysql_num_rows($mysql_result); 
if ($num_rows == 0) { 

$sql ="INSERT INTO table1 (uname,type) VALUES ('$_SESSION[user_name]','$_GET[id]')" or die (mysql_error());
     $result=mysql_query($sql);

*/ The above works fine.  But the below isn't updating the membership table. */

$sql3="SELECT * FROM table1 WHERE '$uname'=uname";
$result = mysql_query($sql3);
$num_rows = mysql_num_rows($result);
$sql4="UPDATE table2 SET counter='$num_rows' WHERE '$uname'=user_name";

*/ The above does not work. */

 if ($result) {
header("Location: http://etc");
} else {
echo "<strong>ERROR: unable to add.</strong>";
}
} else {
header("Location: http://etc");
} 
?>

Scriptmaker

I think you've just got your variables and your column names flip-flopped here.

//correct
$query="SELECT * FROM table WHERE column='$column'";

//incorrect
$query="SELECT * FROM table WHERE '$column'=column";

//corrected version of your code
$sql3="SELECT * FROM table1 WHERE uname='$uname'";
$result = mysql_query($sql3);
$num_rows = mysql_num_rows($result);
$sql4="UPDATE table2 SET counter='$num_rows' WHERE uname='$user_name'";

I'm sure you know this already, but if you just want to add a comment to a single line, // works and is quicker than / /

Also, it looks like you just use two /, but you need to use / then */

bradgrafelman

Scriptmaker wrote:
I think you've just got your variables and your column names flip-flopped here.

He certainly does. Then again, that doesn't matter. 5 + 3 = 8 has correct logic, right? 8 = 5 + 3 is also logically correct.

Anyway, you never actually execute your UPDATE query.

Also, a note about your script: It appears as though your script is vulnerable to SQL injection attacks. You should never allow user-supplied data to be used directly in a query. Instead, you should sanitize it with a function such as [man]mysql_real_escape_string/man.

Scriptmaker

bradgrafelman wrote:
He certainly does. Then again, that doesn't matter. 5 + 3 = 8 has correct logic, right? 8 = 5 + 3 is also logically correct.

Anyway, you never actually execute your UPDATE query.

Thanks Brad. I guess I figured it was the same as defining a variable, but I see why this would be different.

Does it work the same way with the SET portion, or must the set be defined left to right?

Anyway, this taught me a lesson to look at the rest of the code and not just stop when I think I've found the error.

bradgrafelman

It's ok. I hope I didn't sound too condescending with the simple math... that's just the first thing that came to my mind when trying to explain it.

No, the same doesn't apply with UPDATE statements, because the 'SET' portion looks for a column name, an equal sign, and then an expression. In the SELECT statement, however, the entire 'WHERE' section can be considered as one giant expression. The "logic" involved in these queries comes in the expression part.

etoast

Thank you both. So why isn't the UPDATE executing? The first query gets a value like '5' and stores that as $num_rows but then nothing is inserted into the 'counter' column in table2. Is my syntax not correct? 😕

$sql3="SELECT * FROM table1 WHERE uname='$uname'";
$result = mysql_query($sql3);
$num_rows = mysql_num_rows($result);
$sql4="UPDATE table2 SET counter='$num_rows' WHERE user_name='$uname'";

Thank you

etoast

whoops! I fogot the ending:
mysql_query($sql4);

thanks again for your help!
regards

bradgrafelman

Note that you could consolidate that into one single query:

$sql = "UPDATE table SET counter = (SELECT COUNT(*) FROM table1 WHERE uname='$uname') WHERE user_name='$uname'";
$result = mysql_query($sql);

If you only expect 1 user to be edited (e.g. user_name is a UNIQUE column) then you could add a "LIMIT 1" onto the end of the UDPATE query.