Page 1 of 1

Using a PHP variable in a SELECT statement

Posted: Fri Dec 13, 2002 6:21 pm
by jjcrook
Hello,

I have two tables: CD and Genre

create table cd(
id smallint unsigned not null auto_increment,
title varchar(50),
genre_id smallint UNSIGNED NOT NULL REFERENCES genre(id),
primary key (id),
key title (title)
);

create table genre(
id smallint unsigned not null auto_increment,
name varchar(50),
primary key (id),
key name (name)
);

I pass my php the name of the genre eg. 'Rock' and I need it to display all the CDs in that genre:

This is what I have at the moment.
$genreID = mysql_query ("select id from genre where name = $genreName", $connection);

I want to use this select statement to get all the titles in that genre:
$result = mysql_query("SELECT title FROM cd WHERE id = $genreID", $connection);

Can anyone see the problem?
Any help with this is greatly appreciated.

Regards
John

Posted: Fri Dec 13, 2002 7:02 pm
by Bill H

Code: Select all

<?php
$genreID = mysql_query ("select id from genre where name = $genreName", $connection); 
?>
mysql_query doesn't return that kind of result, it returns a pointer.
you need to extract the data:

Code: Select all

<?php
$Result = mysql_query ("select id from genre where name = $genreName", $connection); 
$row = mysql_fetch_array($Result);
$genreID = $row[0];
?>