mysql query help

M

MadJuggla9

2[H]4U
Joined
Oct 9, 2002
Messages
3,515
I've got a small DB that holds quotes for customers wanting a new computer. I finally finished all of the details needed per page for adding, editing, deleting for the moment. I have one problem that I haven't EXACTLY ran into before, but I think there will need to be a more complex statement using JOIN or AS. Without further ado, here's my simple query:
Code: 
$query	= "SELECT c_id, c_first_name, c_last_name, q_id, q_model, p_id, p_base_price, p_sandh, p_monitor, p_other " . 
		  "FROM customers, quotes, pricing " . 
		  "WHERE c_id=q_id=p_id";

It is supposed to grab the info from each table and display it. However the prices are wrong for each person. It grabs the initial persons price quote, and uses that as the rest of them's price. I have checked the DB to ensure the data is correct, and it is. Something isn't differentiating the IDs on the PRICING table.

Code: 
Viewing all quotes (3)
--------------------------------------------------------------------------------

Name Model # Quote ID Price Delete 
Chris Garner NA 1 $14.00 - edit DELETE 
James Warrilow NA 2 $14.00 - edit DELETE 
Ben Milbrath NA 3 $14.00 - edit DELETE 

--------------------------------------------------------------------------------
Home | View All Quotes | Add
 
MadJuggla9 said:
I've got a small DB that holds quotes for customers wanting a new computer. I finally finished all of the details needed per page for adding, editing, deleting for the moment. I have one problem that I haven't EXACTLY ran into before, but I think there will need to be a more complex statement using JOIN or AS. Without further ado, here's my simple query:
Code: 
$query	= "SELECT c_id, c_first_name, c_last_name, q_id, q_model, p_id, p_base_price, p_sandh, p_monitor, p_other " . 
		  "FROM customers, quotes, pricing " . 
		  "WHERE c_id=q_id=p_id";

It is supposed to grab the info from each table and display it. However the prices are wrong for each person. It grabs the initial persons price quote, and uses that as the rest of them's price. I have checked the DB to ensure the data is correct, and it is. Something isn't differentiating the IDs on the PRICING table.

Code: 
Viewing all quotes (3)
--------------------------------------------------------------------------------

Name Model # Quote ID Price Delete 
Chris Garner NA 1 $14.00 - edit DELETE 
James Warrilow NA 2 $14.00 - edit DELETE 
Ben Milbrath NA 3 $14.00 - edit DELETE 

--------------------------------------------------------------------------------
Home | View All Quotes | Add
Click to expand...


What does the table definitions look like? ( And yes.. you probably need a join on the table keys. )
 
quotes:
Code: 
mysql> desc quotes;
+------------+-------------+------+-----+---------+-------+
| Field      | Type        | Null | Key | Default | Extra |
+------------+-------------+------+-----+---------+-------+
| q_id       | int(11)     | NO   | PRI |         |       |
| q_model    | varchar(20) | NO   |     |         |       |
| q_cpu      | varchar(25) | NO   |     | NA      |       |
| q_ram      | varchar(20) | NO   |     | NA      |       |
| q_hd       | varchar(20) | NO   |     | NA      |       |
| q_optical  | varchar(20) | NO   |     | NA      |       |
| q_floppy   | varchar(20) | NO   |     | NA      |       |
| q_kb       | varchar(20) | YES  |     | NA      |       |
| q_mouse    | varchar(20) | NO   |     | NA      |       |
| q_gpu      | varchar(20) | NO   |     | NA      |       |
| q_audio    | varchar(20) | NO   |     | NA      |       |
| q_os       | varchar(20) | NO   |     | NA      |       |
| q_warranty | varchar(20) | NO   |     | NA      |       |
| q_software | varchar(20) | NO   |     | NA      |       |
| q_monitor  | varchar(20) | NO   |     | NA      |       |
| q_other    | varchar(20) | NO   |     | NA      |       |
+------------+-------------+------+-----+---------+-------+
16 rows in set (0.00 sec)

customers:
Code: 
mysql> desc customers;
+--------------+-------------+------+-----+----------+-------+
| Field        | Type        | Null | Key | Default  | Extra |
+--------------+-------------+------+-----+----------+-------+
| c_id         | int(11)     | NO   | PRI |          |       |
| c_first_name | varchar(20) | NO   |     | NA       |       |
| c_last_name  | varchar(20) | NO   |     | NA       |       |
| c_address    | varchar(50) | YES  |     | NA       |       |
| c_phone      | varchar(20) | NO   |     | xxx-xxxx |       |
| c_email      | varchar(35) | YES  |     | NA       |       |
+--------------+-------------+------+-----+----------+-------+
6 rows in set (0.01 sec)

pricing:
Code: 
mysql> desc pricing
    -> ;
+--------------+------------+------+-----+---------+-------+
| Field        | Type       | Null | Key | Default | Extra |
+--------------+------------+------+-----+---------+-------+
| p_id         | int(11)    | NO   | PRI |         |       |
| p_base_price | float(9,2) | NO   |     | 0.00    |       |
| p_sandh      | float(9,2) | NO   |     | 0.00    |       |
| p_monitor    | float(9,2) | NO   |     | 0.00    |       |
| p_other      | float(9,2) | NO   |     | 0.00    |       |
+--------------+------------+------+-----+---------+-------+
5 rows in set (0.00 sec)
 
Each table has an obvious primary key, but what are the foreign keys?
 
mikeblas said:
Each table has an obvious primary key, but what are the foreign keys?
Click to expand...

That is precisely the problem. It looks like he is assuming that each quote/customer/pricing will correspond to each other by having the same id in each table. Hence why he is not using joins and his where clause is "q_id = c_id = p_id".

You need to re-examine your data model. I'm not exactly sure how "pricing" and "quotes" are related, but to take I guess I would think that you would want each customer to have the ability to have multiple quotes. Even if you don't want this behavior you're probably going to want to have a foreign key in your quotes table that points to what customer that quote is for.
 
generelz said:
That is precisely the problem. It looks like he is assuming that each quote/customer/pricing will correspond to each other by having the same id in each table. Hence why he is not using joins and his where clause is "q_id = c_id = p_id".

You need to re-examine your data model. I'm not exactly sure how "pricing" and "quotes" are related, but to take I guess I would think that you would want each customer to have the ability to have multiple quotes. Even if you don't want this behavior you're probably going to want to have a foreign key in your quotes table that points to what customer that quote is for.
Click to expand...

My PKs are the *_id in each table. There is a foreign key between the tables 'quotes' and 'pricing' and between 'quotes' and 'customers'. After each quote is assigned for the first time, the code then creates an instance of a customer and a price ..... we aren't keeping track of customers for any reason, just quotes.


Here's the code, for what it's worth and how each step goes

take_quote.php:
Code: 
<?php
include("library.header");
?>
<font class=title>Take a New Quote</font><hr>
<br>
<br>

<?php
if(isset($_GET["qid"])) {
	$first_name	= $_GET["first_name"];
	$last_name	= $_GET["last_name"];
	$address		= $_GET["address"];
	$phone		= $_GET["phone"];
	$email		= $_GET["email"];
	$qid			= $_GET["qid"];
	
	
	//insert them as a customer
	$query	= "INSERT INTO customers (c_id, c_first_name, c_last_name, c_address, c_phone, c_email) VALUES " . 
			"('$qid', '$first_name', '$last_name', '$address', '$phone', '$email')";
	$result	= mysql_query($query);
	
	//insert the ID for pricing
	$query	= "INSERT INTO pricing (p_id) VALUES ('$qid')";
	$result	= mysql_query($query);
	
	//insert their quote
	$model	= "NA";
	$cpu 	= "NA";
	$ram 	= "NA";
	$hd	 	= "NA";
	$optical	= "NA";
	$floppy 	= "NA";
	$kb	 	= "NA";
	$mouse 	= "NA";
	$gpu 	= "NA";
	$audio 	= "NA";
	$os	 	= "NA";
	$warranty	= "NA";
	$software	= "NA";
	$monitor 	= "NA";
	$other 	= "NA";
	
	$query	= "INSERT INTO quotes " . 
			"(q_id, q_model, q_cpu, q_ram, q_hd, q_optical, q_floppy, q_kb, q_mouse, q_gpu, q_audio, q_os, q_warranty, q_software, q_monitor, q_other) VALUES " . 
			"('$qid', '$model', '$cpu', '$ram', '$hd', '$optical', '$floppy', '$kb', '$mouse', '$gpu', '$audio', '$os', '$warranty', '$software', '$monitor', '$other')";
	$result	= mysql_query($query);
	if(!$result)	echo "Insert FAILED<br>";
	else			echo "Inserted quote record successfully into quotes table<br>";
	
	echo "Quote #<b>$qid</b> for <b>$first_name $last_name</b> has been saved in customers table<br><br>" . 
		"<a href=view_quote.php?view_quote=$qid>Click here</a> to add details";
	
	
	

	
} else { ?>
	<form action=take_quote.php method=GET>
		First Name:<input class=table	type=text	name=first_name><br>
		Last Name:<input class=table	type=text	name=last_name><br>
		Address: 	<input class=table	type=text	name=address><br>
		Phone: 	<input class=table	type=text	name=phone><br>
		Email: 	<input class=table	type=text	name=email><br>
		Quote #: 	<input class=table	type=text	name=qid><br>
		<input type=submit>
	</form>
<?php } ?>

<?php include("library.footer"); ?>


view_all.php:
Code: 
<?php 
include("library.header");
if( isset($_GET["delete"]) ) {
	$del_id	= $_GET["delete"];
	//delete from pricing
	$query	= "DELETE FROM pricing WHERE p_id='$delete'";
	$result	= mysql_query($query);
	//delete from quotes table first
	$query	= "DELETE FROM quotes WHERE q_id='$delete'";
	$result	= mysql_query($query);
	//now delete from customers
	$query	= "DELETE FROM customers WHERE c_id='$delete'";
	$result	= mysql_query($query);
	
	//after deleting, unset the query string vars
	header("Location: view_all.php");
}


[COLOR="DarkOrange"][B]$query	= "SELECT c_id, c_first_name, c_last_name, q_id, q_model, p_id, p_base_price, p_sandh, p_monitor, p_other " . 
		  "FROM customers, quotes, pricing " . 
		  "WHERE c_id=q_id=p_id";[/B][/COLOR]
$result	= mysql_query($query);
$results	= mysql_num_rows($result);
echo "<font class=title>Viewing all quotes ($results)</font><hr><br>";


echo "<table border=1 cellpadding=5 cellspacing=0 bordercolor=#dddddd class=table>";
echo "<tr><td><b>Name</b></td><td><b>Model #</b></td><td><b>Quote ID</b></td><td><b>Price</b></td><td><b>Delete</b></td></tr>";
while($row=mysql_fetch_array($result)) {
	
	$first_name	= $row["c_first_name"];
	$last_name	= $row["c_last_name"];
	$qid			= $row["q_id"];
	$model		= $row["q_model"];
	$total		= $row["p_base_price"] + $row["p_sandh"] + $row["p_monitor"] + $row["p_other"];
	$total		= number_format($total, "2", ".", ",");
	
	echo "<tr>" . 
		"<td><a href=view_quote.php?view_quote=$qid>$first_name $last_name</a></td>" . 
		"<td>$model</td>" . 
		"<td>$qid</td>" . 
		"<td>$$total - <a href=edit_price.php?qid=$qid>edit</a></td>" . 
		"<td><a href=view_all.php?delete=$qid>DELETE</a></td>" . 
		"</tr>";
}
echo "</table>";



include("library.footer"); 
?>
 
I think I see what's going on..

What you want to have set up is this..
each quote is uniquely created by one user.
each quote has a unique price.

that's why there should be a key from the quote to the customer and a key from the quote to the price.

(*_id's = keys)

Quote table;
INT quote_id
INT customer_id
INT price_id

Customer_table:
INT customer_id
VARCHAR customer_name

Price_table:
INT price_id
INT price_amount


SELECT
QT.quote_id
,QT.customer_id
,QT.price_id
,CT.customer_name
,PT.price_amount
,<other columns here>
FROM quote_table QT WITH(NOLOCK)
LEFT JOIN customer_table CT WITH(NOLOCK)
ON CT.customer_id = QT.customer_id
LEFT JOIN price_table PT WITH(NOLOCK)
ON PT.price_id = QT.price_id

That's off the top of my head, from the info you given, this is more or less how I have it set up.
Regardless, take the advice of generalz and rework your data model.
 
There is a foreign key between the tables 'quotes' and 'pricing' and between 'quotes' and 'customers'.
Click to expand...

Hmm, is this in a separate table? Why wasn't it included in your post where you showed the "describe" output for each table? Either way, I still think your data model has some flaws in it. Isn't a "price" simply the sum of all the items in the quote? Why are you modeling this as a separate entity, instead of simply calculating it using a SUM() (perhaps in a view?). Couldn't a user have more than one quote? If so, how would this be stored?

Also, as your code stands right now, your code is wide open to SQL Injection attacks. You are manually building queries and escaping things without knowing the incoming content. It would be trivial for someone to craft a fake data submission and read all your customer's information and quotes, as well as any other data stored in the same database. The good news is this is very easy to solve by using parameter binding.

Don't get discouraged. Small projects like this are great learning experiences. And you have plenty of people here who are happy to help you out.
 
Isn't there a way without redoing the model? It seems as if something much more simplistic can be applied since there is no need for a differing model; I feel I have done this before on similar table relations, I just don't know where the code is located.
 
generelz said:
Hmm, is this in a separate table? Why wasn't it included in your post where you showed the "describe" output for each table? Either way, I still think your data model has some flaws in it. Isn't a "price" simply the sum of all the items in the quote? Why are you modeling this as a separate entity, instead of simply calculating it using a SUM() (perhaps in a view?). Couldn't a user have more than one quote? If so, how would this be stored?

Also, as your code stands right now, your code is wide open to SQL Injection attacks. You are manually building queries and escaping things without knowing the incoming content. It would be trivial for someone to craft a fake data submission and read all your customer's information and quotes, as well as any other data stored in the same database. The good news is this is very easy to solve by using parameter binding.

Don't get discouraged. Small projects like this are great learning experiences. And you have plenty of people here who are happy to help you out.
Click to expand...


The describe results are verbatim, this is mysql so it doesn't show all of the information by default I am guessing. Yes, these are 3 seperate tables.

The price is nothing but a base price that the code will use to generate profit, tax, extras, etc.

The database has only the 3 listed tables, and it's not accessible via the web, so no injections are awaiting unless someone wants to screw themself. If they are wanting to hurt themself, the entire system (code, DBs, other misc stuff) is backed up on various servers located across the US daily. It would be a task and a half to really mess something up.

Thanks for the link and suggestions .... but surely this can be fixed with a simple query change; I'll check my old code tomorrow before I try any redesigning.
 
MadJuggla9 said:
but surely this can be fixed with a simple query change; I'll check my old code tomorrow before I try any redesigning.
Click to expand...

What makes you so sure? Your data model is such that a customer has zero or one quotes, and a quote has zero or one prices. (Or, maybe a customer has zero or one prices. Since you don't describe the domain of the keys, we can't tell.)

If that's what you want, then there's nothing to fix. If there exists one-to-many relationships between customers and quotes, customers and prices, or quotes and prices, you need to fix your data model. It's not a redesign; it's a simple addition of another column.
 
MadJuggla9 said:
Isn't there a way without redoing the model? It seems as if something much more simplistic can be applied since there is no need for a differing model; I feel I have done this before on similar table relations, I just don't know where the code is located.
Click to expand...

In my opinion you still haven't completely nailed down what exactly you want your data model to look like. In this respect you are already headed for failure. This is the first thing you really should think about in the most abstract of senses - in your business domain, how are each of your business objects {Customer, Quote, Pricing} related? Once you decide on the multiplicity and direction of each of these relationships you can begin to map this onto your persistence model. There are well-defined and efficient mappings for each type of relationship between one business object and another, you just need to find which one is the best for your situation and you cannot do this until you adequately describe your business domain.

MadJuggla9 said:
The describe results are verbatim, this is mysql so it doesn't show all of the information by default I am guessing. Yes, these are 3 seperate tables.
Click to expand...

I am not sure what version of mysql you're using, but when I run a describe on my version it shows me ALL my columns including primary and foreign key columns. From what you've shown us, you don't have ANY foreign key columns, and depending on your data model this could pose a problem.

MadJuggla9 said:
The price is nothing but a base price that the code will use to generate profit, tax, extras, etc.
Click to expand...

That doesn't really answer my question, you see? I asked if the price was simply the sum of all the prices of the components in the quote. If that's the case then you may not need to store this information because it is really redundant. What if someone wanted to go back and change the motherboard? This means you not only have to update the quoted component but the overall price as well. Why not have this be a dynamically computed value instead, either by the database or your code?

MadJuggla9 said:
The database has only the 3 listed tables, and it's not accessible via the web, so no injections are awaiting unless someone wants to screw themself. If they are wanting to hurt themself, the entire system (code, DBs, other misc stuff) is backed up on various servers located across the US daily. It would be a task and a half to really mess something up.

Thanks for the link and suggestions .... but surely this can be fixed with a simple query change; I'll check my old code tomorrow before I try any redesigning.
Click to expand...

All of these things you've listed are really just excuses for not wanting to fix the problem in my opinion. The fact remains that your code has an issue with SQL Injection. Not only this, but as your code stands right now it is incapable of storing anything that might need to be escaped when it goes into the database, such as unicode characters, quotes, etc. and that is a serious issue. As a web programmer, I certainly would not feel comfortable with anyone using my code if it suffered from those problems, much less getting paid for it.
 
You can pretend there are no foreign keys, just matching IDs on each table that tie the quote, the price of the quote, and the customer info of the quote together. Again, I appreciate the injection feedback, but that's not my issue right now (if it ever will be for this particular apllication) ..... and assuming can be bad as far as pay goes ... sadly, why do you think it is as simplistic as it is :D


I'm not excusing mishaps that need fixing on a larger hierarchical level, no unicode will be needed, escape chars will be escaped if need be when the time rolls around to 'spruce' up this little application. Little things will be 'fixed' (if you will) later. (the DB itself will NOT change, it will stay very simplistic).

The price will not change as it is predetermined at the time of the quote and ONLY that time. The quotes will be tossed after 1 month or less. The quote is given by a manufacturer at a particular date. I also have an edit feature which show's 'additional' components that takes care of everything 'extra' except the prefab system and the OS.

The FK/PK can be thought of as 3 PKs to simply tie each table together via their *_id, nothing else. MySQL must need a parameter because I copied those exactly from prompt.

I understand that models are important, however I am not headed for failue on such a small limited DB that is fundamentally complete.


mike: all relations are 1:1, it is a very 'simple' or 'dumb' database that doesn't have any of the overhead that it's hyped up to have.


I just need the price of a particular quote ...... :D
Worst come to worse, I can execute a small subquery. I would rather sum it up in one though. If I find my code tomorrow I will post it. Sorry for the lack of info, but this is a very small, all 1:1, db with no cool feature or anything beyond plain jane.
 
MadJuggla9 said:
mike: all relations are 1:1, it is a very 'simple' or 'dumb' database that doesn't have any of the overhead that it's hyped up to have.
Click to expand...
Uh, what hype?

Anyway, if it's all 1:1, why have separate tables?
 
mikeblas said:
Uh, what hype?

Anyway, if it's all 1:1, why have separate tables?
Click to expand...

All are ints. The non PKs are all varchar except for the pricing table; Floats are used there.

Since they are all related to seperate categories, I was thinking more along the lines of why not.
 
Huh? I'm still trying to figure out what hype you're talking about.

As for why not: correctness and efficiency. Ease of querying, too; You seem befuddled only because the query you want to write involves more than one table.
 
SELECT QUOTE.[Q_ID]
,QUOTE.[Q_Quote]
,CUSTOMER.[C_Name]
,PRICE.[P_Price]
FROM [Catalog].[dbo].[Test_Quote] QUOTE with(nolock)
LEFT JOIN [Catalog].[dbo].[Test_Customer] CUSTOMER with(nolock)
on QUOTE.[Q_ID] = CUSTOMER.[C_ID]
LEFT JOIN [Catalog].[dbo].[Test_Price] PRICE with(nolock)
on QUOTE.[Q_ID] = PRICE.[P_ID]

Try that.. I think you are saying that all the *_ids are the same across the board for each entry, and not for the record on each table. Maybe I am reading it wrong, but when you have a new quote/customer/price to enter you slap the *_ids with the same number (even though the column names are different) and start shoving things into tables. Horribly wrong way of doing it, but what ever. THe only issue with the statement above is if there is a record for the quote but not of the customer or price you will get null in those fields. Additionally your system it doesn't allow you to use the same customer for multiple prices or quotes which is straight wrong.

If I am mis interpreting what you are saying, just hollar back.
 
The hype of extra features not being utililized or implemented is what I was talking about. I have also used multiple tables in other queries, so it's not a foreign commodity, just an 'i don't understand why THIS query doesn't work' type post. What modi123 said is exactly right for the relationships; which I thought was clear *shrugs*, it's hard to be in other peoples' head.

Without further ado, the beloved fix was simply breaking apart the triple equality:
Code: 
$query	= "SELECT c_id, c_first_name, c_last_name, q_id, q_model, p_id, p_base_price, p_sandh, p_monitor, p_other " . 
		  "FROM customers, quotes, pricing " . 
		  "[COLOR="DarkOrange"][B]WHERE q_id=p_id AND q_id=c_id[/B][/COLOR]";

I must have been way off in my description, and I apologize. But I wasn't looking for a reconstruction or a change on such a limited DB. I just needed the prices to match each quote/customer as it iterated through 3 tables. Thanks for all the effort put forth and provided help and links.

I bet you guys want to kill me :p
 
You must log in or register to reply here.
Back
Top