PDA

View Full Version : Online store and the relational database


jaymo
02-23-2009, 12:34 AM
This may be a long one so please be patient :)

Ok the project I am doing in college requires a basic online store without worrying about the ecommerce side. I have 3 database tables in phpmyadmin with their fields:

Customers(customer_id, Name, Password, Address etc)
Products(product_id, productName, description, price)
Sales(order_id, date, customer_id, product_id, total)

The colours show the foreign keys linking back to the customer and products table. The basic idea is that users logon and can see the orders they have made. So far I have it so the page shows a dynamic table in dreamweaver that adds rows. These new rows come from the Sales table above so that it displays ONLY the products that the user has bought.

SQL Code:

/////////////////////////////////////
$query = sprintf("SELECT * FROM Customers where customer_id ='$id' ");
$result = @mysql_query($query);
$customers = @mysql_fetch_array($result);
/////////////////////////////////////

/////////////////////////////////////
$query = sprintf("SELECT * FROM Sales");
$result = @mysql_query($query);
$sales = @mysql_fetch_array($result);
/////////////////////////////////////
Dreamweaver table code:

<?php do { ?>
<tr>
<td><?php echo $sales['customer_id']; ?></td>
<td><?php echo $sales['product_id']; ?></td>
<td><?php echo $sales['total']; ?></td>
</tr>
<?php } while($sales = mysql_fetch_array($result)) ?>
Questions:
1. Is this database layout good? The sales table creates a new row for each order, each with a unique order ID. As the user may buy more than one product (one to many).

2. How do I get the dreamweaver table to show data from different tables? Now it shows the product_ids related to the users purchases, as required. But I also want the product description, however it is not in the Sales table, which is where the above results are from. Is my SQL query no good?

Sorry if this is long and confusing but hope someone can help or at least learn something from the above. Cheers

davidj
02-23-2009, 06:53 AM
first of all drop phpmyadmin and get EMS MYSQL Mananger (Free version)

third one down

http://sqlmanager.net/en/products/mysql/manager/download

ok


Customers(customer_id, Name, Password, Address etc)

Products(product_id, productName, description, price)

Sales(order_id, date, customer_id, product_id, total)


you can use joins within your sql to link tables

you can control these query results as you would any other result


SELECT * From Sales LEFT JOIN Products on Products.product_id = Sales.product_id WHERE


check out

ref:
http://dev.mysql.com/doc/refman/5.0/en/join.html

and see what options you have regarding joins

jaymo
02-23-2009, 02:58 PM
Thanks for that David. EMS was my original choice. I only moved to phpmyadmin because the hosts I looked at used it from the Cpanel. EMS is better I agree.

I am looking at using a dynamic table in dreamweaver CS4 to show data. I have a really annoying problem though. When I try to add one I get a popup that says first take these steps...Defin site, choose a document type, setup testing server, create record set. The first 2 are ticked but testing server is not. I have setup a testing server with WampServer and it obviously works because I can use it with F12 and everything. Is this a bug? My setup is as follows:

Local Info:
Local root folder: C:\wamp\www\myWebsite\
images: C:\wamp\www\myWebsite\images
HTTP address: http://localhost/myWebsite/

Remote Info:
Access: Local/Network
Remote folder: C:\wamp\www\myWebsite\

Testing Server:
Server Model: PHP MySQL
Access: Local/Network
Test server folder: C:\wamp\www\myWebsite\
URL Prefix: http://localhost/myWebsite/

Any glaring errors here? I have tried lots of different combinations with this and cant seem to get past it. Hope you can help, cheers

davidj
02-23-2009, 03:17 PM
are you hand coding or using dreamweaver to code for you?

jaymo
02-23-2009, 03:20 PM
up until now I have been hand coding. I made a logon script for example using bits of your tutorial. I'm just thinking it might be easier to use dynamic tables to format database results

davidj
02-23-2009, 03:33 PM
dont dare touch dreamweaver toys

handcode everything

if you want to take this forward i can give you a PHP primer on a one-to-one if you want

pm me