Display Page based on MySQL Information.

[Jumpy09]

Alright I decided I wanted to get a little bit knocked off a Guitar that I want to buy, so I decided to recode the store's website from the start.  Their website is FrontPage made and you can tell someone who's new to coding designed it.  Images placed everywhere, no flow, and to boot it isn't even complete.

 

For a Music Retailer, their stock comes and goes as it will.  Sometimes it's hard to keep up with a website to match what you have in stock, for basic websites.  I'm not planning on coding in a whole bunch of features like a shopping cart or anything, but could use some assistance in creating a script to pull information from a MySQL Database for Listing Items.

 

I'm going to use the $_Get Function with $include's that I am hoping will use MySQL to grab from a list of Categories, and then from the Categories display items, prices, and small pictures in an alphabetized list.

 

Database Table

 

For Inventory ID (** Electric Guitars, Packages, Acoustic Guitars, Amps, Racks, Pedals..ect.. **)

ID / Inventory Category

 

For Brand Names

ID / Brand Name (** Blue Ridge, Gitane, Gibson, Fender, Martin, ... ect ... **)

 

For Items (** Actual Items **)

ID / Inventory ID / Brand ID / Item Name / Item Description / Mini Description / Item Price / Price Range (1-10) / Item Stock (Number) / Item Serial Code / Image

 

I have it so that the Admin can add items to the database.  The problem comes from displaying those items.

 

Basically I need to allow the Customer to choose a Brand Name, Price Range (Or All), Then it show a list of items with a mini description and a mini page.

Then from there, when the Customer clicks on the image or name, they are displayed the full page, with full Description.

 

If you supply some example, I'm pretty sure I'll be able to grasp the rest.  I don't need someone to code the entire thing for me lol.

 

Thanks in Advance.  To my knowledge this is all I will need, but we shall see.

 

 

 

[Jumpy09]

To Clarify:

 

Customer goes to ?page=electric

On this page it automatically pulls a select box full of Brands from SQL

After Selecting Brand Name , Price Range , (Other Things) the page loads a table full of items using itemid (Primary Key) from the items table.

 

There it displays like this.

 

[small Image][item Name][small Item Description][Price][in-Stock?]

 

The In-Stock will say "Yes" or "No" depending on the number in stock.  IE stockid > 0 == Yes  stockid = 0 == No

 

Then from here the user can click on the item image, or name and it will take them to the page displaying a much larger page with a full description.

 

Things I can't figure out:

 

1. Pulling Information dependent on Drop Down List Selection (I know how to supply the drop down list from the sql database.)

2. Having In-Stock claim Yes or No, I'm sure it would be a simple If Else statement, but I can probably manage that one on my own.

 

I think I just figured out how to display the page from the list page.  I'd need to do a post to submit the information from one page to the next, then have it display from there with the results.  Even though I think I may have it... I'm not sure.

 

As I said, examples will work.  One example for having 1 thing from the database show from a drop down list will grant me the ability.  One example for posting (using itemid primary key) to the other page will grant me the ability.  Of course, I'll work on #2.. Probably something simple.

[Anti-Moronic]

Sadly, if you don't even know where to start with this, you're not going to get very far and any input from users here (except a full solution) will be wasted.

 

I suggest you hire somebody to do this for you or read some php/mysql tutorials. Also, asking the question directly in google might get you some answers.

 

Hope that helps.

 

 

[Jumpy09]

Sadly, if you don't even know where to start with this, you're not going to get very far and any input from users here (except a full solution) will be wasted.

 

I suggest you hire somebody to do this for you or read some php/mysql tutorials. Also, asking the question directly in google might get you some answers.

 

Hope that helps.

 

Been using Google, but I'm already working on a ton of other stuff for the site.  I don't need a full solution, just direction.  I already know how to input the data into the database and show the data in a table... the problem is just getting it to display what is needed.  It's like when you go on a page and select a Country / Then the Stats show up. 

 

Either way I just started SQL this morning... I think I've made some great progress.

 

And a redirect to Google Never helps, have you actually typed in a search there?  It's like going into a 300 acre plot of land to find 1 thing, sometimes you span 100 acres before finding it... other times you keep looking at the same stuff hoping that you missed something.

 

The only reason I posted here, was because I was trying to get something done before 12PM.  Since it's after 12pm...  I think I may take some time to look around Google to find an answer, of course if someone is willing to help limit my search to something closely resembling what I am trying to do.. then that would be awesome.

[Anti-Moronic]

And a redirect to Google Never helps, have you actually typed in a search there?  It's like going into a 300 acre plot of land to find 1 thing, sometimes you span 100 acres before finding it... other times you keep looking at the same stuff hoping that you missed something.

 

I use google all the time. It IS like a 300 acre plot of land and you might be looking for a blade of grass...but you forget, you have a birds eye view of the place and each blade of grass is tagged. Type in the right thing, you can find anything.

 

I'll reason with you, the answer to this requires too much time and I don't see many people willing to work with you on this until you grasp it. Mainly because of how you've presented it. It's a biggish task (in terms of what is usually asked on this forum) and you have not one line of code.

 

I would advise you repost or edit your thread and present the board with one small problem at a time. Then put the jigsaw together. I know you don't expect people to do the work for you, but even a hint of that and people are instantly offended. That's generally what your thread hints at.

[Jumpy09]

Nah I understand, I've been refining what I have.  I am headed out atm, but I will update this later with some code.  Showing what I have, thanks though.

[Jumpy09]

This is the Table that Displays items.

<table width="700px"border="0" cellspacing="2" cellpadding="2">
<tr>
<th><font face="Arial, Helvetica, sans-serif">ID</font></th>
<th><font face="Arial, Helvetica, sans-serif">Category</font></th>
<th><font face="Arial, Helvetica, sans-serif">Brand</font></th>
<th><font face="Arial, Helvetica, sans-serif">Name</font></th>
<th><font face="Arial, Helvetica, sans-serif">Description</font></th>
<th><font face="Arial, Helvetica, sans-serif">Price</font></th>
<th><font face="Arial, Helvetica, sans-serif">In-Stock?</font></th>
<th><font face="Arial, Helvetica, sans-serif">Serial Code</font></th>
</tr>

<?
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM items";
$result=mysql_query($query);
$num=mysql_numrows($result);
$i=0;
while ($i < $num) {

$itemid=mysql_result($result,$i,"itemid");
$cid=mysql_result($result,$i,"cid");
$bid=mysql_result($result,$i,"bid");
$itemname=mysql_result($result,$i,"itemname");
$itemdesc=mysql_result($result,$i,"itemdesc");
$itemprice=mysql_result($result,$i,"itemprice");
$itemstock=mysql_result($result,$i,"itemstock");
$itemsc=mysql_result($result,$i,"itemsc");
?>

<tr>
<td><font face="Arial, Helvetica, sans-serif"><? echo $itemid; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $cid; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $bid; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $itemname; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $itemdesc; ?></font></td>
<td><font face="Arial, Helvetica, sans-serif">$<? echo $itemprice; ?></a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $itemstock; ?></a></font></td>
<td><font face="Arial, Helvetica, sans-serif"><? echo $itemsc; ?></a></font></td>
</tr>

<?
$i++;
}


echo "</table>";

?>

 

This is the Code that Inserts Categories/Brands/Items.

<form action="includes/insertcategory.php" method="POST">
<table class="left" width=500px">
	<tr><td width="100px">Category Name:</td><td><input type="text" name="itemname"></td></tr>
	<tr><td>Category Description:</td><td><input type="text" name="itemprice">In Progress!</td></tr>
	<tr><td><input type="Submit" value="Submit"></td><td></td></tr>
</table>
</form>

<form action="includes/insertbrand.php" method="POST">
<table class="left" width=500px">
	<tr><td width="100px">Brand Name:</td><td><input type="text" name="itemname"></td></tr>
	<tr><td>Brand Description:</td><td><input type="text" name="itemprice">In Progress</td></tr>
	<tr><td><input type="Submit" value="Submit"></td><td></td></tr>
</table>
</form>

<form action="includes/insertitem.php" method="POST">
<table class="left" width=500px">
	<tr><td>Category:</td><td><select name="category">
<?php
$sql = "SELECT cid, catname FROM categories ".
"ORDER BY catname";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['cid']."\">".$row['catname']."\n  ";
}
?></select></td></tr>
	<tr><td>Brand Name:</td><td><select name="brand">
<?php
$sql = "SELECT bid, brandname FROM brands ".
"ORDER BY brandname";

$rs = mysql_query($sql);

while($row = mysql_fetch_array($rs))
{
  echo "<option value=\"".$row['bid']."\">".$row['brandname']."\n  ";
}
?></select></td></tr>
	<tr><td width="100px">Item Name:</td><td><input type="text" name="itemname"></td></tr>
	<tr><td>Item Price:</td><td><input type="text" name="itemprice"></td></tr>
	<tr><td>Item Availability:</td><td><input type="text" name="itemstock"></td></tr>
	<tr><td>Item Serial Code:</td><td><input type="text" name="itemcode"></td></tr>
	<tr><td>Item Image Name:</td><td><input type="text" name="itemimage"></td></tr>
	<tr><td>Item Description:</td><td><textarea cols="40" rows="5" name="itemdesc">Item Description</textarea></td></tr>
	<tr><td><input type="Submit" value="Submit"></td><td></td></tr>
</table>
</form>

 

This is the Function/Process for Inserting Items

<?php

$cat=$_POST['category'];
$brand=$_POST['brand'];
$itemname=$_POST['itemname'];
$itemdesc=$_POST['itemdesc'];
$price=$_POST['itemprice'];
$stock=$_POST['itemstock'];
$code=$_POST['itemcode'];
$image=$_POST['itemimage'];

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query = "INSERT INTO items VALUES ('','$cat','$brand','$itemname','$itemdesc','$price','$stock','$code','$image')";
mysql_query($query);

mysql_close();
echo 'Insert Success';
?>

 

How's that?   I think I managed pretty nicely for just starting with SQL Today.

But anyway.  I'm thinking about setting up multiple Item Tables... [Acoustic Items][Electric Items][bass Items][Amps Items][Drum Items][Vintage Items]  But the problem is every time a Category is included, a new table would have to be added.  Too much ground work.

 

So From the Database Tables posted on Post #1, I've called the database information to spawn a drop down list.  That drop down list also includes the cid (Cat ID) and bid (Brand ID) automatically when the new item is posted.

 

So now, I have to 1. Find a way to have the cid from the items table match with the cid from the category table, to display category / same with brand.. but meh, once you figure one out.. you figure the other one out too.  2. Refine how information is called from the database to display only what is wanted (( I.E. Specific CID's, Specific BrandIDs ))

 

So much fun.

[Anti-Moronic]

Without reading too much into the code, I'll help where I can in the 5 min window I have here.

 

Yeh, you are doing very good for just starting out with sql. To be honest, sql is one of the easiest parts of php and databases. You'll get it in no time and it only increases in difficulty when you have to do more complex queries.

 

first - I am no absolute expert so I may be giving less than great advice at times.

 

==

<?php

//Now, the sql you have:

$query = "INSERT INTO items VALUES ('','$cat','$brand','$itemname','$itemdesc','$price','$stock','$code','$image')";

//Is that working? From what I know and use, this is the standard:

INSERT INTO table (field1,field2,field3) VALUES ('val1','val2','val3')

//Also, there is no need to have '' in your query for the first field if you have your table field set as NULL etc by default.

===

//So now, I have to 1. Find a way to have the cid from the items table match with the cid from the category table, to display category / same with brand.. but meh, once you figure //one out.. you figure the other one out too.  2. Refine how information is called from the database to display only what is wanted (( I.E. Specific CID's, Specific BrandIDs ))

//1. is achieved via a kind of join query (of which I have little experience). It works like so:

SELECT * FROM table1,table2 where table1.id=table2.id

//That will fetch everything from both tables where they both match the field 'id' which can of course now be CID.


//You can also select specific items from either table:

SELECT table1.field1,table2.field5 FROM.. etc etc

//2. I think you mean just fetch what you need via sql (not via php to define what to fetch)..but not sure so I'll explain.

//You can use the SELECT above to fetch specific details like so:

$query = "SELECT table1.field1,table2.field5 FROM table1,table2 where table1.id=table2.id";
$data = mysql_query($query) or die(mysql_error()); 

# only use or die above when debugging queries.

// check number of results

if(mysql_num_rows($data)>0){
$fdata = mysql_fetch_assoc($data);
}

//If there is only 1 row - ie a specific product etc. then that will work. But if there is more than one result for the query you will need to loop:

while($fdata = mysql_fetch_assoc($data)){
print_r($fdata);
}

//Now, in $fdata you have an array which mirrors your table(s) and can be called like so:

echo $fdata['field1'];

//You can test the entire array by doing this:

print_r($fdata);

?>

====

 

I really do hope that helps. Best bet is to keep running queries and spitting out results. If you have any other questions or I missed something, like what you were trying to do, let me know and I'll do my best to help when I get time.

[Jumpy09]

That actually helps out quite a bit.

 

As for the INSERT thing you asked about, I ended up having a problem with it because I forgot to set the id fields to be auto-incremental.  Searched all over the place and wound up with:

$query = "INSERT INTO `items` (cid, bid, itemname, itemdesc, itemprice, itemstock, itemsc, image)  VALUES ('$cat','$brand','$itemname','$itemdesc','$price','$stock','$code','$image')";

 

The original one worked, but I thought there may have been an issue with it when it wasn't inserting anything more than the first one.  Somehow by a simple auto-increment issue, I managed to end up with what you suggested.  Of course, I would have refined it once you mentioned it.  I found the first one on a website, which is where I found most of this stuff..  Go Me for being a Noob .

 

=====

 

As for:

$query = "SELECT table1.field1,table2.field5 FROM table1,table2 where table1.id=table2.id";
$data = mysql_query($query) or die(mysql_error());

 

That works for individual pages, defining what to call.  If I go with a single page, using $table1 ; $table2 and have the Drop Down List define the variables it could work.  My mind stopped working a few hours ago.  I'm thinking use the Drop Down List with a Submit Button, have the post direct it back to the same page to change variables.  Sounds like it would work .

 

All in all I think this may very well solve most of my issues.  Still kind of stuck with this one though:

 

SELECT * FROM table1,table2 where table1.id=table2.id

It would select and match results, and I'm really trying to think here. 

 

After I display the results from the items list.

ID - CatID - BrandID

 

I was wondering how to pull the CatID (2nd field) from `items` and check it with CatID (1st field) from `categories` and display CatName (2nd field).

I'm thinking an IF statement would work, may want to check me on this.

if($table1.cid=$table2.cid) {
echo '$table1.catname';
}

 

Something tells me that isn't right lol, looks right but I suppose I'd have to test it to see.  Of course my mind has stopped functioning for now, thanks for the code..  I'll try a few things and see where I get, .  But, if SQL is one of the easiest parts... then after this site is done (still hoping to get a great deal on that guitar) I may give up.    Actually most of this will help on another site I am working on.  Thanks again .

 

Last thing: If anything sounds off, I just noticed table1.field1 was just field1 but in the same position as table1.  Took me a while, most likely will have to refine my little if statement.. which I'll do later.. I need a break lol.

[Anti-Moronic]

I'll reply to that tomorrow when I get some time. But I tell you what, if you can send over an export of the database or allow access to a test server...I will construct a few queries to help you better understand how you should be going about this.

 

The code is all good but sometimes I just need something in front of me on a web page before my mind starts working.

[Jumpy09]

I'll reply to that tomorrow when I get some time. But I tell you what, if you can send over an export of the database or allow access to a test server...I will construct a few queries to help you better understand how you should be going about this.

 

The code is all good but sometimes I just need something in front of me on a web page before my mind starts working.

 

I sent you a Private Message with the Export from the database.  Thanks.

[Jumpy09]

Sorry for double post, but I've been trying to figure this out.

 

The following code is an attempt to pull information from one table in the database, match one field from it to another field in another table.. then display a 2nd field from the first table.  Looks right, but it's a little confusing.

 

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$cid="1";
$cid1=mysql_query("SELECT cid FROM `categories` WHERE cid = '$cid'");
$cid2=mysql_query("SELECT cid FROM `items` WHERE cid = '$cid'");
$cname=mysql_query("SELECT catname FROM `categories` WHERE cid = '$cid2'");

If ($cid1==$cid2) {mysql_result($cname);};

 

Basically I am using cid as a category indicator which is also put into the items table.  Instead of it showing a 1, 2, 3 or 4, I want it to display the category name.

So the table layout is like.

 

categories = [cid][catname]  EX: 1 Electric Guitar

items = [itemid][cid][bid][itemname]  EX 1 1 2 Ibanez IJX121

So if cid from items = cid from categories DISPLAY catname from categories.

 

So the new item display would be like EX: 1 Electric Guitar 2 Ibanez IJX121

I'm going to do the same thing to bid which is the brand id.

 

Do I have this code right?  I doubt it.. and I hate being new to this stuff.

 

Thanks for the help ><.