Join Query problem.

[rogerzebra]

Hi friends,
I have problem to get a inner join query to work. I start to wonder if I'm missing a driver or so, to make join queries? When i try a simple select it shows me the information.
My tables are Inno db. As always I really appreciate if someone could take a moment to look at my problem.

$colname_rsArticles = "-1";

	if (isset($_GET['id_art'])) 
	{
  	$colname_rsArticles = (get_magic_quotes_gpc()) ? $_GET['id_art'] : addslashes($_GET['id_art']);
	}

mysql_select_db($database_db, $db);
$query_rsArticles = sprintf("SELECT * FROM blg_article_art INNER JOIN blg_topic_top ON idtop_art = id_top WHERE id_art = %s", $colname_rsArticles); 
$rsArticles = mysql_query($query_rsArticles, $db) or die(mysql_error());
$row_rsArticles = mysql_fetch_assoc($rsArticles);
$totalRows_rsArticles = mysql_num_rows($rsArticles);

Thanks
/rz

 

[ericbrunson]

Run the query in mysql shell or mysqladmin and then show us the error code. It's probably more likely an issue with your PHP than mysql.

 

[rogerzebra]

ericbrunson,
okay...this is what i get in phpmyadmin.

#1064 - 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 '%s, $colname_rsArticles LIMIT 0, 30' at line 1

Que???..anyway my server version is MySQL 4.1.7

 

[azzazzello]

what is %s supposed to be?

 

[rogerzebra]

%s", $colname_rsArticles....it suppose to print the value in the variable $colname_rsArticles. which it doesn't. here is rest of the code.

<?php
mysql_select_db($database_db, $db);
$query_rsTopoics = "SELECT * FROM blg_topic_top ";
$rsTopoics = mysql_query($query_rsTopoics, $db) or die(mysql_error());
$row_rsTopoics = mysql_fetch_assoc($rsTopoics);
$totalRows_rsTopoics = mysql_num_rows($rsTopoics);

$colname_rsArticles = "-1";

	if (isset($_GET['id_art'])) 
	{
  	$colname_rsArticles = (get_magic_quotes_gpc()) ? $_GET['id_art'] : addslashes($_GET['id_art']);
	}

mysql_select_db($database_db, $db);
$query_rsArticles = sprintf("SELECT * FROM blg_article_art INNER JOIN blg_topic_top ON idtop_art = id_top WHERE id_art = %s", $colname_rsArticles); 
$rsArticles = mysql_query($query_rsArticles, $db) or die(mysql_error());
$row_rsArticles = mysql_fetch_assoc($rsArticles);
$totalRows_rsArticles = mysql_num_rows($rsArticles);
?>

<td width="69%"><?php echo $row_rsArticles['title_top']; ?>&gt;<?php echo $row_rsArticles['title_art']; ?></td>
          <td width="31%">&nbsp;Published:<?php echo $row_rsArticles['date_art']; ?></td>
        </tr>
        <tr> 
          <td colspan="2"><?php echo $row_rsArticles['description_art']; ?></td>
        </tr>
        <tr> 
          <td colspan="2"><?php echo $row_rsArticles['text_art']; ?></td>

 

[TonyGroves]

It looks like your sprintf function is not working as you expect. You might want to look up the syntax. In any event, you don't need sprintf; you could just use:
[tt]
$query_rsArticles =
"SELECT * FROM blg_article_art INNER JOIN blg_topic_top ON idtop_art = id_top WHERE id_art = $colname_rsArticles";
[/tt]

 

[rogerzebra]

thanks for trying... but I still can't get it to work. Do I need a certain driver in order for joins queries to work??

 

[TonyGroves]

You certainly don't need a special driver; your SQL is interpreted by the MySQL server, and anyway it's a very simple query.

Just one thing - should there be quotes around $colname_rsArticles ?

 

[rogerzebra]

tonyG...
thanks again for you time...I have tried with quotes around the variable without luck. I maybe should show you my tables...here they are.

CREATE TABLE `blg_article_art` (
  `id_art` int(11) NOT NULL auto_increment,
  `idtop_art` int(11) NOT NULL default '0',
  `title_art` varchar(100) NOT NULL default '',
  `description_art` varchar(255) NOT NULL default '',
  `text_art` longtext NOT NULL,
  `date_art` datetime default NULL,
  PRIMARY KEY  (`id_art`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=23 ;

-- 
-- Dumping data for table `blg_article_art`
-- 

INSERT INTO `blg_article_art` VALUES (1, 0, 'test articles', 'test', 'this is a test', '2000-04-13 12:40:58');

CREATE TABLE `blg_topic_top` (
  `id_top` int(11) NOT NULL auto_increment,
  `title_top` varchar(100) NOT NULL default '',
  `description_top` varchar(255) NOT NULL default '',
  PRIMARY KEY  (`id_top`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=6 ;

-- 
-- Dumping data for table `blg_topic_top`
-- 

INSERT INTO `blg_topic_top` VALUES (1, 'Redwood', 'Redwood Classes');

/rz

 

[TonyGroves]

The error message was about the syntax of the query you had assembled in your code, so that's where you need to concentrate your attention. If you insert a "print" (or whatever the PHP equivalent is) statement in the code after the query has been assembled, but before it's executed, then that will show you the text of the query that will be attempted. If that doesn't give you any clues, then you could post the text of the query here.

 

[rogerzebra]

okay...here is the debug output

sql string is SELECT * FROM blg_article_art INNER JOIN blg_topic_top ON blg_article_art.idtop_art = blg_topic_top.id_top WHERE blg_article_art.id_art='-1'

 

[TonyGroves]

That syntax looks fine. Older versions (before 4.0 at least) of MySQL didn't support the INNER JOIN ... ON syntax, but that's not an issue here.

Is it still the same error message that you reported initially?

 

[rogerzebra]

..it's actually doesn't give me any error message at all. it's just a blank space together with the rest of the layout.

 

[TonyGroves]

You could have told us the syntax error had been fixed.

I've no idea why nothing is produced; maybe there really are no matching records.

 

[rogerzebra]

I'm sorry for not telling you earlier that the syntax error was fixed. The records are matching. I guess I have to over things again and see what's wrong. I'll let you know.
Thanks