Page 1 of 1
Problem with SQL query?
Posted: Mon Feb 16, 2009 8:36 pm
by Hendeca
Hi,
I've been having problems with bind_param() for a mysqli connection. I looked at some other threads and noticed that many people got the same error (bind_param on a non-object) and that often times it was because the SQL statement was written incorrectly.
Here's the code:
Code: Select all
$currentPage = basename($_SERVER['SCRIPT_NAME'], '.php');
$conn = new mysqli('connection', 'user', 'pwd', 'database');
$st = $conn->prepare('SELECT * FROM ? ORDER BY photo_id DESC');
$st->bind_param("s", $currentPage);
$result = $st->execute();
Later when I try to fetch_assoc from $result, I'm unable to. I tried a var_dump for $st right after the prepare statement and found that it returned false. I'm assuming this means that the SQL query is incorrect. Anyone know what's wrong with it? I'd also love to find a good reference of how to write proper SQL queries, as there seem to be some rules I don't understand.
Thanks!
Re: Problem with SQL query?
Posted: Tue Feb 17, 2009 8:57 am
by VladSun
http://rpbouman.blogspot.com/2005/11/my ... x-and.html
You can't just put a placeholder wherever you like. Parameter placeholders can appear only in those places inside the statement where you would normally expect an expression. In particular, you cannot use parameter placeholders to parameterize identifiers or entire statement structures. So, the following attempt fails with a syntax error, because the parameter placeholder appears where you would normally put an identifier:
mysql> prepare stmt from 'create table ? (id int unsigned)';
ERROR 1064 (42000): 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 '? (id int unsigned)' at line 1
Re: Problem with SQL query?
Posted: Tue Feb 17, 2009 2:40 pm
by Hendeca
Ah! That explains a lot! So this is for a photography site where the photographs and their names and info are contained in various tables. The table names correspond to the photo categories. For example if it was the people category, the table name is "people". My plan was to name each photo category page the same name as the table, so that I could use the $_SERVER[SCRIPT_NAME] variable to select photos from that specific table. Is this possible, or would I need to make a single table and categorize all the entries by which photo category their in? For example would I have to make an extra row named photo_category and use the bind_param function to set the table name to the $_SERVER[SCRIPT_NAME] variable?
Essentially, is there any way to use bind_param to dynamically select a table, or would I have to change my database organization so that the bind_param could be used where an expression is expected?
Re: Problem with SQL query?
Posted: Tue Feb 17, 2009 3:12 pm
by VladSun
The table names correspond to the photo categories. For example if it was the people category, the table name is "people". My plan was to name each photo category page the same name as the table, so that I could use the $_SERVER[SCRIPT_NAME] variable to select photos from that specific table.
Wrong way
would I need to make a single table and categorize all the entries by which photo category their in? For example would I have to make an extra row named photo_category and use the bind_param function to set the table name to the $_SERVER[SCRIPT_NAME] variable?
Essentially, is there any way to use bind_param to dynamically select a table, or would I have to change my database organization so that the bind_param could be used where an expression is expected?
The right way

Except that I don't know why you should use $_SERVER[SCRIPT_NAME] - elaborate, please.
Probably, you will have to categorize one picture into multiple categories. So, I would advice you not to put an extra field "photo_category" (because this way you can assign only one category per picture), but to create three tables:
Code: Select all
picture : id, name, file_name, etc ...
category: id, name, etc ...
category_picture: FK_picture_id, FK_category_id
So, you could create several
category_picture records per picture, this way assigning several categories to a picture.
Re: Problem with SQL query?
Posted: Tue Feb 17, 2009 6:42 pm
by Hendeca
Ok that's great advice! The reason I would use $_SERVER[SCRIPT_NAME] was that the page names had the same name as the tables for each category. For example if the page name was people.php, I could use $pageName = basename($_SERVER['SCRIPT_NAME'], '.php') to create a string variable containing the table name.
In the case of this site, I know that I won't need more than one category per picture because they are essentially grouped in photo sets and the photographer only wants each photo in one single set. However, I'm a little confused as to how to use the three tables. I'm confused as to why there's both a category table and a category_picture table. Would the picture table include the photo info, the category table include which photos are in each category, and the third use information from both tables to produce a list? I suppose I just need some more explanation.

I'm still a bit of a php mysql newb (even after a lot of study!) I would really appreciate it because one of my main problems is organizing scripts and databases in a way that works in the long term. Thanks!
Re: Problem with SQL query?
Posted: Tue Feb 17, 2009 7:03 pm
by VladSun
Hendeca wrote:In the case of this site, I know that I won't need more than one category per picture because they are essentially grouped in photo sets and the photographer only wants each photo in one single set.
Then you could put the category relation into the picture table:
Code: Select all
picture : id, name, file_name , FK_category_id, etc...
category: id, name, etc...
Hendeca wrote:However, I'm a little confused as to how to use the three tables. I'm confused as to why there's both a category table and a category_picture table. Would the picture table include the photo info, the category table include which photos are in each category, and the third use information from both tables to produce a list? I suppose I just need some more explanation.

I'm still a bit of a php mysql newb (even after a lot of study!) I would really appreciate it because one of my main problems is organizing scripts and databases in a way that works in the long term. Thanks!
The
picture table containts information about the picture itself (no category information), the
category table containts information about the category itself (no "picture-members" information), and the third table -
category_picture contains the relationship information between pictires and categories (FK stands for "foreign key").
Imagine you have 2 pictures ( 1.house and 2.people-in-front-of-a-house) and 2 categories (people, houses):
Code: Select all
picture
id name
1 house
2 people&house
category
id name
1 people
2 houses
category_picture
FK_picture_id FK_category_id
1 2
2 1
2 2
Re: Problem with SQL query?
Posted: Wed Feb 18, 2009 3:45 am
by Hendeca
Thanks for the explanation! I didn't even know that foreign keys existed, and I'm now currently in the process of learning how to create them in MyPHPAdmin.
Because I'm still looking into foreign keys, I went ahead and created a new table that contains all the photos and includes a category field. In the script for one of the photo pages I was able to get everything to work, except when I tried to bind two variables in an sql query (I also switched over to PDO for now). It looks like this:
Code: Select all
$currentPage = basename($_SERVER[SCRIPT_NAME], '.php');
if (isset($_GET['image'])) {
$currentImage = $_GET['image'];
$result = $db->prepare('SELECT * FROM photos WHERE file_name = ? AND category = ? ');
$result->bindParam(1, $currentImage);
$result->bindParam(2, $currentPage);
$result->execute();
$currentEntry = $result->fetch(PDO::FETCH_ASSOC);
This works when the second condition (category = ?) is removed, but not when it is there. Is my sql syntax wrong? Or perhaps the bindParam function? Thanks for everything!
Re: Problem with SQL query?
Posted: Wed Feb 18, 2009 8:43 am
by VladSun
I haven't work with PDO, but according to the examples you need something like this:
Code: Select all
$result = $db->prepare('SELECT * FROM photos WHERE file_name = ? AND category = ? ');
$result->bindParam(1, $currentImage, PDO::PARAM_STR, 20);
$result->bindParam(2, $currentPage, PDO::PARAM_STR, 20);
$result->execute();
http://bg2.php.net/manual/en/pdostatement.bindparam.php
I would strongly advice you to use integer IDs for primary keys and to use them in WHERE clauses in your queries.