Pass table name as variable

[Hikaru12]

I have a shopping list program. Each list corresponds to a table. Problem is I don't know ahead of time what the names of the tables/lists are going to be called. Right now how I've solved this is by having a textbox where the user types in the name of the list they wish to see and that's saved as a variable. The SQL Statement "SELECT * FROM $somelist" is used to then find the one that the user typed in. As you can see it's not the most efficient way of editing lists.

 

If I do something like:

SELECT TABLES FROM testdb;

I get a list of tables. I'd like the list as follows:

Table1 (when clicked, view the contents of the table) | Edit | DeleteTable2 (when clicked, view the contents of the table) | Edit | Delete...etc

I've heard that PDO in PHP can take in SQL sections and instead replace them with variables but I'm not sure how that works.

 

BTW: This is done in PHP with MySQL

[Guest]

If I understand correctly, you want to store one shopping list per table. Don't.
 
You should be storing your shopping list names in one table, and shopping list items in another table. Build the relationships between tables and items with a third join table that contains shopping list item IDs and the corresponding shopping list ID.

 

I haven't verified these queries work, but they should be enough to get you started. You'll need to read up on auto-incremented primary keys, foreign keys, etc.

- Relation: shopping_list+-----------+| id | name |+-----------+- Relation: shopping_list_item+------------------------------+| id | name | quantity | price |+------------------------------+- Relation: shopping_list__shopping_list_item+------------------------------------------+| shopping_list_id | shopping_list_item_id |+------------------------------------------+- Query shopping lists:SELECT id, name FROM shopping_list;- Query shopping list items in a shopping list:  The '?' below is a placeholder for use of this query with bound  parameters, which you should also read about.SELECT id, name, quantity, priceFROM(  SELECT shopping_list_item_id  FROM shopping_list__shopping_list_item  WHERE shopping_list_id = ?) AS listINNER JOIN shopping_list_item  ON shopping_list_item_id = shopping_list_item.id;

[Hikaru12]

 

If I understand correctly, you want to store one shopping list per table. Don't.

 

You should be storing your shopping list names in one table, and shopping list items in another table. Build the relationships between tables and items with a third join table that contains shopping list item IDs and the corresponding shopping list ID.

 

I haven't verified these queries work, but they should be enough to get you started. You'll need to read up on auto-incremented primary keys, foreign keys, etc.

- Relation: shopping_list+-----------+| id | name |+-----------+- Relation: shopping_list_item+------------------------------+| id | name | quantity | price |+------------------------------+- Relation: shopping_list__shopping_list_item+------------------------------------------+| shopping_list_id | shopping_list_item_id |+------------------------------------------+- Query shopping lists:SELECT id, name FROM shopping_list;- Query shopping list items in a shopping list:  The '?' below is a placeholder for use of this query with bound  parameters, which you should also read about.SELECT id, name, quantity, priceFROM(  SELECT shopping_list_item_id  FROM shopping_list__shopping_list_item  WHERE shopping_list_id = ?) AS listINNER JOIN shopping_list_item  ON shopping_list_item_id = shopping_list_item.id;

 

I have the following code. I used prepared statements for everything and redid my table layout. I can add items to the grocery list and manually execute a query that marks the item as purchased but when it comes to actually hitting the "Mark as bought" button it doesn't update. What it should do is mark a bought with an id of 1 which means it was purchased and strike out the item. Mind taking a look? Thanks for the help so far.

 

Attached grocery script

[Guest]

I have the following code. I used prepared statements for everything and redid my table layout. I can add items to the grocery list and manually execute a query that marks the item as purchased but when it comes to actually hitting the "Mark as bought" button it doesn't update. What it should do is mark a bought with an id of 1 which means it was purchased and strike out the item. Mind taking a look? Thanks for the help so far.

 

Attached grocery script

 

I don't see anything immediately wrong with it. Have you run your SQL directly on the database to verify it works?

[Hikaru12]

I don't see anything immediately wrong with it. Have you run your SQL directly on the database to verify it works?

 

Yes. The query works directly. Just doesn't work when the href is clicked.

[Guest]

Yes. The query works directly. Just doesn't work when the href is clicked.

 

Print statements to report the values of variables in the query on submission?