PDA

View Full Version : multiple tables


tony09uk
04-19-2012, 09:41 PM
I have two tables
users
user_id. user_fname. user_lname. username, password
article
article_id, user_id, title, content, date

I have set up an input field for a title and a text field for content. My user has to be logged in to create a new entry but I can only get the title, content and date field to update with the necessary information. i would also like user_id to be updated, how do I do it?

So far I have created this code which works but does not update the user_id, could someone please advise on how to do that please:
$title = $_POST['title'];
$content = $_POST['content'];
mysql_query("INSERT INTO article(title, content, date) VALUES ('$title', '$content', NOW())") or die("Could not update!")

edbr
04-20-2012, 02:10 AM
use a join for the tables or a view to do the same

tony09uk
04-20-2012, 10:03 AM
Yeah, i considered that, but things are quickly becoming more complex, I will take a look and come back to you for further advice

jmichae3
04-23-2012, 08:16 AM
joins are equivalent to WHERE with a common column and table. Joins are only good for 2-table queries to my knowledge.
For instance, this 3-table query cannot be done with join in any way I know of in one statement (someone please correct me if I am wrong):

and by the way, this is a wrong-headed way to do it, hierarchical data like this is best done by querying each table individually else you get lots of repeated rows of higher echelons of hierarchical data (such as vendor_name).
hierarchy of tables:
vendors (
vendor_id INT AUTO_INCREMENT,
name TEXT
),
products (
product_id INT AUTO_INCREMENT,
name TEXT,
price decimal(20,2),
vendor_id INT
);

customers (
customer_id INT AUTO_INCREMENT,
fname TEXT,
lname TEXT,
phone VARCHAR(30)
),
orders (
order_id INT AUTO_INCREMENT,
invoice_number INT
),
lineitems (
lineitem_id INT AUTO_INCREMENT,
qty INT,
product_id int,
sell_price decimal(20,2)
),
orders_lineitems (
ol_id INT AUTO_INCREMENT COMMENT 'relationship table',
orders_id INT,
lineitem_id INT
),
customers_orders (
co_id INT AUTO_INCREMENT COMMENT 'relationship table',
customer_id INT,
orders_id INT
);


this database MIGHT be too normalized for average joe to make an app on (but I wouldn't discourage anyone from trying) - the idea for the structure basically comes from SQL:The Complete Reference (but not the code) - it's a good book on SQL to have as a reference and cookbook. I have a bad habit of making things 4th and 5th normal form and then not being able to implement something because it's too normalized...

this is a query to list the orders for a particular customer. really, this should be broken up into several quewries, at least a query for the customer info and a query for the order info, and a query for the lineitem info.


-----select a particular customer so you can display the order. this should be done one time at the start of the need for this info dump.
SELECT
customers.fname AS fname,
customers.lname AS lname,
customers.phone AS phone,
FROM
customers
WHERE
customer.customer_number='12';


-----get the customer orders info.make this your outer loop
SELECT
orders.invoice_number AS invoice_number,
orders.order_id AS order_id,
FROM
orders,
customers_orders
WHERE
customers_orders.customer_id='$customernumber' AND
customers_orders.order_id=orders.order_id
"for every order" {


-----get the lineitem info. I suggest that this be done in an inner loop.
SELECT
vendors.name AS vendor,
products.name AS desc,
lineitems.qty AS qty,
lineitems.sell_price AS price,
lineitems.qty*lineitems.sell_price AS extprice
FROM
orders,
lineitems,
vendors,
products
WHERE
orders.order_id='$orderid'
orders_lineitems.orders_id=orders.order_id AND
orders_lineitems.lineitems_id=lineitems.lineitem_i d;


}








sometimes you have to break your data down so you are not duplicating rows (not always, but it saves space and increases speed, and can be a royal headache to implement and maintain).

there may need to be a GROUP BY in there somewhere, I would need to do some manual testing to be sure, GROUP BY does some weird things with rows...

jmichae3
04-23-2012, 09:51 AM
I have two tables
users
user_id. user_fname. user_lname. username, password
article
article_id, user_id, title, content, date

I have set up an input field for a title and a text field for content. My user has to be logged in to create a new entry but I can only get the title, content and date field to update with the necessary information. i would also like user_id to be updated, how do I do it?

So far I have created this code which works but does not update the user_id, could someone please advise on how to do that please:
$title = $_POST['title'];
$content = $_POST['content'];
mysql_query("INSERT INTO article(title, content, date) VALUES ('$title', '$content', NOW())") or die("Could not update!")


sorry for my previous post. while instructive, it has nothing to do with the question. :-/

article should really be names articles. table names are typically plural since there can be more than 1 row.

date is a reserved word. it's a data type.you are going to have to use something like last_login_timestamp or a
last_updated DATETIME
this would give the person a date and time of when they last updated their article too!
mysql_query("INSERT INTO article(user_id,title,content,last_updated) VALUES
(
(SELECT user_id FROM users WHERE username='".mysql_real_escape_string($_SESSION['username'])."' AND password='".sha1($_SESSION['password'])."'),
'".mysql_real_escape_string($_SESSION['title'])."',
'".mysql_real_escape_string($_SESSION['content'])."',
NOW()
)",$link);

actually, I would sha1 the password before storing it in a session to save on memory in case somebody is trying to break in...
sha1 is better than md5. less hash collisions.

a sample goes like this:

INSERT INTO article(user_id,title,content,last_updated) VALUES
(
(SELECT user_id FROM users WHERE username='joepl' AND password='moreblah'),
'joe palooka goes for a run',
'joe went for a run today.',
NOW()
)

mysql> show columns from users;
+------------+--------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------+--------------+------+-----+---------+----------------+
| user_id | int(11) | NO | PRI | NULL | auto_increment |
| user_fname | text | YES | | NULL | |
| user_lname | text | YES | | NULL | |
| username | varchar(100) | YES | | NULL | |
| password | text | YES | | NULL | |
+------------+--------------+------+-----+---------+----------------+
5 rows in set (0.02 sec)

mysql> show columns from article;
+--------------+----------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+----------+------+-----+---------+----------------+
| article_id | int(11) | NO | PRI | NULL | auto_increment |
| user_id | int(11) | YES | | NULL | |
| title | text | YES | | NULL | |
| content | text | YES | | NULL | |
| last_updated | datetime | YES | | NULL | |
+--------------+----------+------+-----+---------+----------------+
5 rows in set (0.03 sec)

mysql>


if you want to update both tables simultaneously, you can do it with the standard UPDATE syntax. (see the mysql manual)
UPDATE `Table A`,`Table B`
SET `Table A`.`text`=concat_ws('',`Table A`.`text`,`Table B`.`B-num`," from ",`Table B`.`date`,'/')
WHERE `Table A`.`A-num` = `Table B`.`A-num`


so, you could do:

UPDATE article,users
SET
article.title='joe palooka goes on a scenic trip',
article.content='joe palooka got in his car and went for a country trip.',
article.last_updated=NOW()
WHERE
article.article_id='1' AND
article.user_id=(SELECT users.user_id FROM users WHERE users.username='joepl' AND users.password='moreblah') AND
users.user_id=(SELECT users.user_id FROM users WHERE users.username='joepl' AND users.password='moreblah');


mysql> select * from article;select * from users;
+------------+---------+-----------------------------------+---------------------------------------------------------+---------------------+
| article_id | user_id | title | content | last_updated |
+------------+---------+-----------------------------------+---------------------------------------------------------+---------------------+
| 1 | 2 | joe palooka goes on a scenic trip | joe palooka got in his car and went for a country trip. | 2012-04-23 01:45:04 |
+------------+---------+-----------------------------------+---------------------------------------------------------+---------------------+
1 row in set (0.00 sec)

+---------+------------+------------+----------+----------+
| user_id | user_fname | user_lname | username | password |
+---------+------------+------------+----------+----------+
| 1 | joe | schmoe | jschmoe | blah |
| 2 | joe | plotz | joepl | moreblah |
+---------+------------+------------+----------+----------+
2 rows in set (0.00 sec)

mysql>

jmichae3
04-23-2012, 09:55 AM
this is called nested SQL. it was newly introduced to MySQL possibly about version 5. it's a very welcome addition, because you can do some really cool things!

edbr
04-23-2012, 10:41 AM
I have two tables
users
user_id. user_fname. user_lname. username, password
article
article_id, user_id, title, content, date

did i misread his post? using left join i believe you can join multiple tables but i will check

jmichae3
04-23-2012, 11:09 AM
joins are only used in SELECT statements where you have a condition.
INSERT statements don't have a condition like SELECT does.
I think this is because INSERT doesn't really NEED a condition (think about it). all it needs to do is shove data rows into the table. and that's exactly what it does. exactly HOW you accomplish that is up to you... :-) could be using a nested select... :-)
http://dev.mysql.com/doc/refman/5.6/en/left-join-optimization.html
http://dev.mysql.com/doc/refman/5.5/en/insert.html
http://dev.mysql.com/doc/refman/5.5/en/select.html