Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Page 1 of 3 123 LastLast
Results 1 to 15 of 33
  1. #1
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Product table through to item listing

    Not sure if this belongs in PHP or mySQL

    I looking for some pointers in how this is carried out.

    I have a html table that is populated from a mySQL database. What I want to do is that when you click on a product in the table it takes you to a product page that is filled out by fetching that specific products details.

    So using a common template, if say "apples" is clicked on, it would load up the common template, and fill in all the detail from the SQL database about that product "apples"

    What Im not sure on if how to make it fetch only the product info for what has been selected

    Any help would be hugely appriciated

    Cheers

    Russ

  • #2
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Either or since both will apply.
    The initial page will be responsible for designing a link which includes the querystring of the productid. I'll call that productid, so that would simply generate a link for each entry in the iteration of something like: showproduct.php?productid={primarykeyhere}. Then on showproduct.php, you simply check if $_GET['productid'] has been provided, and if so, you perform whatever validation you need and then query the db by providing it on the WHERE clause. The rest is the same, you can even loop it per normal even though it only generates one record. Then you simply populate the fields you need and go from there.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #3
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Hi Thanks, so from my below html table code, I would just to a href on each listing?


    Code:
    <?php 
    	$connect = mysql_connect("localhost","*****", "******");
    			if (!$connect) {
                    die(mysql_error());
                }
                mysql_select_db("my_database");
    
    // select default
    	$Default = 'size';
    
    // select array
    	$Columns = array('image', 'part_num','size','style','voltage');
    
    // define sortable query ASC DESC
    	$sort = isset($_GET['sort']) && in_array($_GET['sort'], $Columns) ? $_GET['sort'] : $Default;
    	$order = (isset($_GET['order']) && strcasecmp($_GET['order'], 'DESC') == 0) ? 'DESC' : 'ASC';
    
    	$result = mysql_query("SELECT * FROM my_products WHERE type='new' AND vtype='DC' ORDER BY $sort $order");
    ?>
    
    <table width='100%'>
    	<tr>
    		<th width="25%">Product Image</th>
    		<th width="10%">Part Number</th>
    		<th width="15%">Size</td>
    		<th width="15%">Style</td>
    		<th width="15%">Voltage</td>
    	</tr>
    	
    <?php
    // list records
    while ($row = mysql_fetch_assoc($result)) {
    	echo "
    		<tr class='right-cell'>
    			<td class='centered-cell'><img src = '".$row[image]."'/</td>
    			<td class='centered-cell'>$row[part_num]</td>
    			<td>$row[size]</td>
    			<td>$row[style]</td>
    			<td>$row[voltage]</td>
    
    		</tr>";
    }
    ?>
    </table>

  • #4
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    That's correct. You'd simply add a new table column, and then have a <td><a href='showproduct.php?productid={$row['productid']}'>Edit</a></td> to it. Where showproduct.php is the script name you want, productid is the primary key of the table, and $row['productid'] is the primary key value to dereference.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #5
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Great, will give this a go.

    Just out of interest, can I have the link from the part number, so if they click on the part number then it goes to product page, or does it need to be a seperate column

  • #6
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Nope, you can link whatever you want. All that matters is that its the primary key of the record so you can look up a single value on the script.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #7
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    thank you, appriciate your help

    At the moment I just use the standard sequential id number at the primary key. Is this recommended, or is it better to have your primary key as the part numbers of the product?

  • #8
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    Surrogate keys are debatable. I personally find them a waste and will only consider them if I exceed a three property composite key when I use a flattening table for simplicity.
    If part number is always unique, which I assume it would be, than I'd suggest the use of part number to be the primary key of the table instead of the surrogate.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #9
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    Great, I will do that, thank you

  • #10
    New Coder
    Join Date
    Jan 2013
    Posts
    23
    Thanks
    0
    Thanked 0 Times in 0 Posts
    It is really awesome forum post and I really like to read it; it is really very informative post by you and thanks for sharing to all.

  • #11
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    I was trying to do too many things at once, so only just getting in to the nitty gritty of this now!!

    I have the tables all setup with page links as per the above convo. I have started the product detail page layout, but have a quick question.

    How do you 'Get' data from the SQL database that relates specifically to the part number that is in the URL.

    Would I use the SELECT command to select the specific row? If so, how do you get it to select the primary key thats been clicked on from the table.

    For example, in the below URL, I want to select the row at product id = 1, and then print all the data from the columns

    'http://tester.com/showproduct?productid=1

    Cheers

  • #12
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    You simply select using the WHERE clause. Since you provide a unique field, you know that at most one result will be returned.
    PHP Code:
    $id = isset($_GET['productid']) ? (int)$_GET['productid'] : 0;
    if (
    $id 0)
    {
        
    $sQry "SELECT field, field2, field3, [. . .] FROM table WHERE productid = $id";
        
    // runs the query and pull the data

    Simple as that. You'll need to actually write the handling for the display and everything as well for each particular detail for the entry in the database.
    I'd suggest looking at mysqli as well. It allows prepared statements within it, which are far more secure against SQL injection since the data is detached from the structure. Plus the Mysql library will be removed in a version in the near future (I expect but cannot say for sure that by 6.0 it'll be gone).
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • Users who have thanked Fou-Lu for this post:

    RussH (04-30-2013)

  • #13
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    am I doing something in correct with echo'ing out the rows with the following

    while ($row = mysql_fetch_assoc($sQry)) {

    Ive used your code above, and change the values etc. But cant seem to get it to print the data?

  • #14
    God Emperor Fou-Lu's Avatar
    Join Date
    Sep 2002
    Location
    Saskatoon, Saskatchewan
    Posts
    16,980
    Thanks
    4
    Thanked 2,659 Times in 2,628 Posts
    If you are using $sQry as I have above, then you are trying to retrieve a resultset from a string. You need to execute it with mysql_query first, and use the resultset resource to pass to the mysql_fetch_* methods.
    PHP Code:
    header('HTTP/1.1 420 Enhance Your Calm'); 

  • #15
    New Coder
    Join Date
    Apr 2013
    Posts
    32
    Thanks
    2
    Thanked 0 Times in 0 Posts
    oh ok, I think I see what you mean.

    By the way, I echo'ed out the $id just to ensure I was getting the right id number, and that part doesnt seem to be working.

    So in the product table, I have a column that you click on to get to the product page. This is simplyt the products part number. The code I use is,

    <td class='centered-cell'><a href='http://tester.com/showproduct?productid={$row['part_num']}'>$row[part_num]</a></td>

    Then, I added in the echo of the $id that you have shown above and it doesnt seem to read the part number. It just reads '0'

    Is this cause I need to set the code to the following

    $id = isset($_GET['partnum']) ? (int)$_GET['partnum'] : 0;
    if ($id > 0)





    Edit: Actually, could this be because I have the primary key (product id) as the part number, which is an alpha numeric value
    Last edited by RussH; 05-01-2013 at 12:09 AM.


  •  
    Page 1 of 3 123 LastLast

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •