[SOLVED] Trouble matching up fields in different tables

[ShopMAster]

Hey guys, I'm getting an error with the following query:

 

$query = "SELECT COUNT(*) FROM feedback where match = ' ".$_GET['chid']." '  ";

 

I have the field 'match' in a feedback table and is defined like this:

 

CREATE TABLE feedback (
  fid int(11) NOT NULL auto_increment,
  rating varchar(25) NOT NULL default '',
  `comment` varchar(100) NOT NULL default '',
  `date` datetime NOT NULL default '0000-00-00 00:00:00',
  giver int(5) NOT NULL default '0',
  `to` int(5) NOT NULL default '0',
  `match` int(4) unsigned NOT NULL default '0',
  UNIQUE KEY fid (fid)

 

The match is supposed to match the 'id' field in the challenge table which is defined like this:

 

CREATE TABLE challenge (
  id int(4) unsigned NOT NULL auto_increment,
  challenger int(4) unsigned default '0',
  opponent int(4) unsigned default '0',
  playdate datetime default NULL,
  `status` int(3) unsigned default '0',
  amount float default '0',
  details varchar(255) default '0',
  system varchar(55) default NULL,
  madden varchar(55) default NULL,
  game int(4) unsigned default NULL,
  hteam varchar(255) default NULL,
  gteam varchar(255) default NULL,
  claimdate datetime NOT NULL default '0000-00-00 00:00:00',
  w int(4) NOT NULL default '0',
  l int(4) NOT NULL default '0',
  KEY id (id,challenger,opponent,playdate,`status`,game)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

 

 

So how do I get the 'match' and the 'id' field from the two tables to line up.  I know that's the problem but I don't know how to fix it.  Thanks.

 

Shopmaster

[per1os]

First of all using get data straight in a query is never a good idea due to SQL Injection, always mysql_real_escape_string that data.

 

Second Numbers should not have single quotes around them in mysql, it can produce weird results sometimes. If you know the field is an integer do not encapsulate it in quotes.

 

Third, let's see if an error is being produced to do this you need something like below.

 

<?php
$query = "SELECT COUNT(*) FROM feedback where match = ".mysql_real_escape_string($_GET['chid']);

$res = mysql_query($query) OR DIE(mysql_error());
?>

 

If that didn't work what MySQL error are you getting?

 

--FrosT

[ShopMAster]

Thanks for the advice, especially the first one I always thought that was some security risk, but didn't know how to get around it.

 

BTW - here is the error that I'm getting:

 

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 '= 2858' at line 1

[per1os]

the column Match must be a reserved word in MySQL either use this query:

 

$query = "SELECT COUNT(*) FROM feedback where `match` = ".mysql_real_escape_string($_GET['chid']);

 

or change the column match name to srchmatch or something similar.

 

--FrosT

[ShopMAster]

Thanks that looks to have fixed it but I'm getting the following error when I echo the $results:

 

Resource id #15 what does that mean.

 

Here is the code:

 

$query = "SELECT COUNT(*) FROM feedback where 'match' = ".mysql_real_escape_string($_GET[chid]). " and giver = ".mysql_real_escape_string($_SESSION[id]);
                                                $result = mysql_query($query) OR DIE(mysql_error());
                                                $row = mysql_fetch_array ($result, MYSQL_NUM);
                                                $num_results = $row[0];   
                                                if ( $num_results == 1 )
                                                     $action = "You have already left feedback.";
                                                else
                                                     echo $result;
                                                     $action = "<b><a href=\"givefeedback.php?chid=".$_GET['chid']."&uid=".$vl['4']."&gb=$gb\"><img border=0 src=images/feedback_but.gif width=162 height=35></a></b>";

 

I have data in the table and have the Session[id] as a giver, but it's not displaying 1 for count.

                                             

 

[per1os]

The result returned a resource id. To get data you need to use mysql_fetch_array or mysql_fetch_assoc IE:

 

<?php
$res = mysql_query($query);

while ($row = mysql_fetch_array($res)) {
//processing here
}

?>

 

--FrosT

[ShopMAster]

I did use a fetch_array:

 

$query = "SELECT COUNT(*) FROM feedback where 'match' = ".mysql_real_escape_string($_GET[chid]). " and giver = ".mysql_real_escape_string($_SESSION[id]);
                                                $result = mysql_query($query) OR DIE(mysql_error());
                                                $row = mysql_fetch_array ($result, MYSQL_NUM);
                                                $num_results = $row[0];  

 

Still getting the error and the echo is still Resource id #15, am I coding it wrong?

[per1os]

You want to echo, $num_results not $result.

 

--FrosT

 

[ShopMAster]

$num_results gives me 0 but there is 1 row that should be affected by the query.

[ShopMAster]

Thanks Frost.