Stuck with PDO query

MySQL database
#1

Hello all,

I’m working on my first PHP project which uses MySQL, but I’ve ran into a slight problem with one of my PDO queries. I’m trying to insert data into a table but the table name isn’t always the same, I thought I could bind a parameter to the prepared statement such as:

[php]
$table = “test”;
$product = $db->prepare(“INSERT INTO ? (?,?) VALUES (?,?)”);
$product->bindParam(1,$table);
[/php]

But after checking on google I realised that this does not work. So I’m trying to use a variable in the query instead without using bindParam, but I get a SQL syntax error. Here is my code:

[php]
function add_new_item($item_name,$item_price,$item_category) {

require("../inc/database.php");

switch($item_category) {
	case "graphics":
		$table = "graphics";
		break;
	case "hard_drives":
		$table = "hard_drives";
		break;
	case "motherboards":
		$table = "motherboards";
		break;
	case "operating_system":
		$table = "operating_system";
		break;
	case "processors":
		$table = "processors";
		break;
	case "ram":
		$table = "ram";
		break;
}

$namecolumn = $table . "_name";
$pricecolumn = $table . "_price";

try {
	$product = $db->prepare("INSERT INTO $table (?,?) VALUES (?,?)");
	$product->bindParam(1,$namecolumn);
	$product->bindParam(2,$pricecolumn);
	$product->bindParam(3,$item_name);
	$product->bindParam(4,$item_price);
	$value = $product->execute();
} catch(PDOException $e) {
	echo $e->getMessage();
	exit;
}

return $value;

}
[/php]

What am I doing wrong?

Thanks,
Adam

#2

Your second code attempt clearly shows you have a bad database design. If your DB was correct you would not even need to do what your trying to do. Take a minute and learn about database normalization and then get the database right. No point in going any further until you do.

#3

Why is that bad database design?

#4

In a nutshell, you are repeating the same type of data in multiple places. It should only be in one place. If you look up “Database Normalization” as I suggested you will understand.

#5

Agree with Kevin.

If you had a table that stored the cat_id and category.

Then a table that had the cat_id in the table. Let’s say that the record with cat_id 2 is RAM.

You would have a query along the lines of,
[php]
“SELECT
item_name,
item_price,
FROM
tbl_parts
WHERE
cat_id = 2”[/php]

That would bring up the names and prices of all of your RAM from a single table.

#6

I have tables for each category e.g. tbl_operating_system, tbl_ram, tbl_graphics etc. They each contain names and prices, and on my products table I have linked these tables using foreign keys e.g. product_name, ram_id, motherboard_id, operating_system_id all corresponding to the ids in the separate tables. For this forum post I was simply asking why the variable wasn’t working in the query. I have a form where the user can enter a new product name and its category from a drop down box, that’s the reason for the switch statement because the user can select any category from the form.

Anyway I fixed the query after having another look at the SQL syntax error.

#7
I have tables for each category e.g. tbl_operating_system, tbl_ram, tbl_graphics etc. They each contain names and prices

And that is the biggest flaw of what you are doing. So if you ever do an app with a thousand categories, you are going to create and maintain a thousand tables?

#8

Well I have to store each category in a table, where else am I going to store the information?

#9

The categories should be in a (single) category table, suggest you read up on db normalization :slight_smile:

#10

Well now I’m really stuck, I’m currently using mysql joins to link up components to products e.g. a product might have a processor id of 1, linking to the processors table and retrieving the processors information, if I have one table for categories, how do I link up the information? Really confused myself now.

#11

What is the common link between the categories?

#12

It would be easier to propose a db structure if you showed what you had.

If you want better input then please add a schema of the relevant tables to sqlfiddle.com, add a few rows of test data and a query that selects the data as you select it today.

(y)

#13

Here’s an SQLFiddle of what my database structure looks like, basically a table for each category and I’m using foreign keys to link the hardware in each category table to each product in the products table.

SQLFiddle:
http://sqlfiddle.com/#!2/466fa6/3

#14

All the products should be in a products table. All the Categories should be in a Category table. Then you just need a joiner table with only the product_id & category_id. When you are repeating data, you can be sure you are doing it wrong.

#15

Alternative layout

[code]
CREATE TABLE product (
id int auto_increment primary key,
name varchar(200),
img varchar(200),
price decimal(10,2)
);

INSERT INTO product (name, img, price)
VALUES (‘Test Product’, ‘img/products/1.png’, ‘199.99’);

CREATE TABLE product_part (
product_id int,
part_id int
);

INSERT INTO product_part
(product_id, part_id)
VALUES
(1, 1),
(1, 2),
(1, 3);

CREATE TABLE part (
id int auto_increment primary key,
part_type_id int,
name varchar(200),
price decimal(10,2)
);

INSERT INTO part
(part_type_id, name, price)
VALUES
(1, ‘Nvidia Graphics’, ‘70.00’),
(2, ‘Asus Motherboard’, ‘80.00’),
(3, ‘Intel Core i5 Processor’, ‘89.99’);

CREATE TABLE part_type (
id int auto_increment primary key,
name varchar(200)
);

INSERT INTO part_type (name)
VALUES (‘video card’), (‘motherboard’), (‘processor’);[/code]

#16

#17

JimL, that table layout looks much better, however I can’t figure out the JOIN statement, how can I select the product name and price along with its parts?

Sponsor our Newsletter | Privacy Policy | Terms of Service