php variable help within mysql

[hosker]

I am trying to extract a value that is stored in a variable from my database. It will not work with how I have the code written. If I replace .$tournament with what I want it to find, it will give me the correct result. ANy suggestions?

 

 

Below is my code:

$result = mysql_query("SELECT id FROM `2012_tournaments` WHERE tournament = .$tournament");

while($row = mysql_fetch_array($result))
  {
  echo $row['id'];
  };

[trq]

What is that full stop doing there?

[hosker]

nothing, even if I remove it, I do not get the result I am looking for.

[trq]

I assume the tournament field is a text type then. String need to be quoted in sql.

 

$result = mysql_query("SELECT id FROM 2012_tournaments WHERE tournament = '$tournament'");

[hosker]

I get this:

 

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in D:\Hosting\6027795\html\demo\submit.php on line 86

 

the actual value I am looking for is an int.

 

 

[trq]

This means that your query is failing and your failing to check it succeeds before passing it to mysql_fetch_assoc().

 

What does mysql_error() display?

[hosker]

mysql_error() retured this:

 

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''2012_tournaments' WHERE tournament = ''' at line 1

 

 

Server version: 5.0.91

[trq]

Post you current code.

[hosker]

The code below works.

$result = mysql_query("SELECT id FROM `2012_tournaments` WHERE tournament = '$tournament'");

while($row = mysql_fetch_array($result))
  {
  echo $row['id'];
  };

 

After looking through the remaining code, I realized that I was not storing the variable $tournament before I was calling the function, thus the error. It works now. Thanks for walking me through the steps to re-examine my code.

[trq]

Your still not checking the query has succeeded before using it's results. This is a terrible habit to get into. Your code should be...

 

if ($result = mysql_query("SELECT id FROM 2012_tournaments WHERE tournament = '$tournament'")) {
  if (mysql_num_rows($result)) {
    while($row = mysql_fetch_array($result)) {
      echo $row['id'];
    }
  }
}

[hosker]

again, thanks for the tips.

 

I also cannot get the following code to work:

 

$sql = "SELECT * FROM weekly_picks WHERE tournament = '$tournament' AND usr = '$usr'";
$result = mysql_query($sql);
echo mysql_num_rows($result);
mysql_error();
if	($num > 0)
mysql_query("UPDATE weekly_picks SET player = '$golfer' WHERE tournament = '$tournament' AND user = '$usr'");
else
mysql_query("INSERT INTO weekly_picks (t_id, tournament, user, player, backup, timestamp) VALUES ('$t_id', '$tournament', '$usr', '$golfer', '$backup', '$time')",$link) or die('Error, insert query failed');

 

I have a form, and when it is submitted I would like to check to see if the $tournament variable already exists in the table along with the userid. IF it does, I would like to update the golfer field with the new $golfer. If it does not, insert a new row. Any suggestions?

 

I get this response:

 

Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in D:\Hosting\6027795\html\demo\submit.php on line 125

 

 

[trq]

Did you read my previous reply?

[hosker]

yes I did, sorry to no have commented, I had that post up prior to me reading it. I put that code in and it worked and I got the result I was looking for.

[hosker]

well, I just thought I would let you know that from your earlier help, I adapted that code to fit the current issue I was trying to resolve and the following code is doing exactly what I want it to

 

if ($result2 = mysql_query("SELECT * FROM weekly_picks WHERE tournament = '$tournament' AND user = '$usr'")) {
  if (mysql_num_rows($result2)) {
    while($row2 = mysql_fetch_array($result2)) {
    mysql_query("UPDATE weekly_picks SET player = '$golfer', backup = '$backup', timestamp = '$time' WHERE tournament = '$tournament' AND user = '$usr'");
    }
  }
  else
  {
  mysql_query("INSERT INTO weekly_picks (t_id, tournament, user, player, backup, timestamp) VALUES ('$t_id', '$tournament', '$usr', '$golfer', '$backup', '$time')",$link) or die('Error, insert query failed');
}}