Using a PHP variable in a SELECT statement

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
jjcrook
Forum Newbie
Posts: 1
Joined: Fri Dec 13, 2002 6:21 pm
Location: London

Using a PHP variable in a SELECT statement

Post 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
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Post 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];
?>
Post Reply