[Resolved] Select a record by ID

[GIJew]

Hello again all,

So I have been working on my site and what a surprise, I thought of something else that might make it better. This is what I was thinking.

All of my records are being displayed on my page just fine. What would be really neat if I could throw the ID number on there and make it a link. You would be able to select the link and get another page with more information.

I looked around to see if I could modify a "READ MORE" script. I could not find one surprisingly. I'm not even sure if that is the right way to go.

So my question is, can you help me? If so, will you help me? I hope that I have given enough information. Thank you in advance for any help you hopefully give me.

[dalecosp]

You've done a bit with explaining, but you haven't really said that much about 1] your content and 2] the organization of your database.

In looking for scripts to modify, have you looked at "Content Management Systems"?

A simple example from a basic CMS:

$sql="select id, headline from stories";
$result=mysql_result($sql);
while $row=mysql_fetch_array($result) {
     echo "<a href=\"page.php?id=".$row['id']."\">";
     echo $row['headline']."</a><br>";
}

HTH,

[GIJew]

Hey HTH,

I knew I was missing something. Let me continue with a bit of background information now.

The content I am providing is for an inventory system we have at work. It displays inventory by Item Number, Description, Location, Quantity, Minimum Stock Level, & Maximum Stock Level.

I have it so it gives you different information based on what type of report. I'm not always displaying the same content either. I'm not doing anything special, just my querys change.

My database is setup like:

DB: Inventory
Table: Items
Columns: inv_id, item_no (varchar), description (varchar), location (char), quantity (int), min_stock_level (int), max_stock_level (int)

An example of the code in my page is as follows:

<?php

//Set the page title and include the HTML header.

$page_title = 'Inventory Database {-Inventory Below Minimum Stock Level-}';
include('./includes/header.php');

// Database Connection 
include ('./includes/db.php'); 
?>

<table border="0" cellspacing="0" cellpadding="0" id="mm_bodytbl" align="center">
	<tr>
	  <td align="center">


<?php 
//Grab the amount of records in the database
$result = mysql_query("select * from inventory where Quantity_onHand < Min_Stock_Level");
$num_rows = mysql_num_rows($result);
echo '<span class="mm_num_rcd">' . $num_rows . '&nbsp;' . 'records are being displayed.' . '</span><br><br>';

//Printer Friendly Version Link
echo '<a href="pf_inv_min.php" target="_blank">PRINTER FRIENDLY VERSION</a><br><br>';

$queryOut = mysql_query("select * from inventory where Quantity_onHand < Min_Stock_Level order by Item_No"); 

while($row=mysql_fetch_array($queryOut)) 
  { 
	//Build table for each record.
  echo '<table id="mm_display" align"center"><tr><td width="30%">';

//Display records
  echo 'Item No.:' . '</td><td>' . $row['Item_No'] . "</td></tr><tr><td>"; 
	echo 'Description:' . '</td><td>' . $row['Description'] . "</td></tr><tr><td>";
  echo 'Quantity:' . '</td><td>' . $row['Quantity_onHand'] . "</td></tr><tr><td>"; 
  echo 'Minimum Level:' . '</td><td>' . $row['Min_Stock_Level'] . "</td></tr><tr><td>"; 
  echo 'Maximum Level:' . '</td><td>' . $row['Max_Stock_Level'] . "<br>"; 

//End table
echo "</td></tr></table><br>";
  }
?>

</td>
</tr>
</table>




<?php include ('./includes/footer.php') ?>

I was thinking of inserting the inv_id right above the item_no as a link so it would bring up more fields. I want to add the following fields to another table (vendorid, vendor, price, etc.). Once the id was clicked, the new fields would be visible.

I thought of looking at a content management thing (I even looked at my postnuke page in the template, couldn't do it) but I'm not sure what to look for in the code. I'm pretty good at seeing what I want on a page and grabbing what I need. Just not this time.

Any help provided would be super appreciated. Thanks again. Anymore info you need, please let me know.

[dalecosp]

Originally posted by GIJew
I was thinking of inserting the inv_id right above the item_no as a link so it would bring up more fields. I want to add the following fields to another table (vendorid, vendor, price, etc.). Once the id was clicked, the new fields would be visible.

I thought of looking at a content management thing (I even looked at my postnuke page in the template, couldn't do it) but I'm not sure what to look for in the code. I'm pretty good at seeing what I want on a page and grabbing what I need. Just not this time.

Any help provided would be super appreciated. Thanks again. Anymore info you need, please let me know.

OK. The link you should be able to handle rather easily --- the question is, what is done as a result of clicking it? You've outlined that as well --- display specific information about the product that needs to be ordered.

Your first issue is one of design. Should the product-specific information be displayed on the same page, or on another page? I'd suggest another script.

<?php

//detail.php --- display details about inventory items

$id=$_GET['id'];  // unless this web is external, GET is OK

// get info from db

$sql="select * from another_table where item_num=".$id;
$result=mysql_query($sql);

//display info

include 'yourheader';
?>Whatever here....

<?php
include 'yourfooter';
?>

It's really not much more difficult than what you've got already...most of the concepts that are in your "out of stock" inventory script can be used again here Give it a shot!

[GIJew]

Hey Dale,

I am just trying to get some clarification. Now...

To provide a link to the detail.php am I to use a normal link pointing to this page? I'm not sure exactly how to do it all. (I'm learning though so please bare with me).

Also, I am trying to fully comprehend the script you gave me. Let me know if I am doing okay with this.

/**************************************************/
$id=$_GET['id'];  //stores the id in a variable from the id of (not sure if it's from inventory or inv_detail table) the record.
/**************************************************/
$sql="select * from another_table where item_num=".$id; //Query the db and grab the id number that was stored in the variable earlier.
$result=mysql_query($sql); //store the query in a variable $result
/**************************************************/
//I am assuming that I put all of my rows down below this.

I guess I am looking for clarification because I am not sure how I would write a link to this page so that it will grab the id number of that record and display the other fields based off of that.

Thanks again and remember, don't hit him, I'm fragile.

[dalecosp]

I'll try and be gentle... if you're new to PHP, it may be a good time to find some good reading material on the subject. There are good tutorials on this site (the site itself, not the forum) Zend.com, any number of sites with "dev" in the URL (devshed, weberdev, etc. etc.) and other places. Google is your friend 😉

Anyway, I'll try and elaborate. On your original page (items out of stock) you'll set things up so that each time you loop and print out an item, you create a link kinda like

echo "<a href= \"page.php?id=".$your_id_variable."\">Click for details</a>";

The "page.php" page I sketched out is real barebones, as you've noticed 😉 but the idea is that you read the item number ($id) from the GET string in the URL (as an aside, that's the reason for the comment regarding intranet vs extranet and security, although that's another ball of wax entirely) and then query the database for the details of that item; finally, you echo out the details selected by the database. I'd suggest you giving it a go and reporting back any problems.

[GIJew]

I found something similiar while I was sitting around the office that I thought I could give a go. It is very similiar to your suggestion (I found it on codewalks.com).

I grabbed a piece of code that was used for an online news system. It had a section for comments, when clicking on the comments link, it brought up a section for the comments.

I am trying to get it to work on my stuff now. I can get the url right, I can get it to grab its OWN id but not the one of the inv_details table. I'm still working on that.

Thank you for the explanation as well. I have all these books and tutorials that tell you how to do it (some in much detail) but putting it all together by yourself is the challenge.

One of these days I shall be able to create something other than a "look at what I made mom" site. I'm getting there though and without the help of all you great people, I would have a lot harder time learning.

I'll post my results (hopefully successful) within the hour.

[GIJew]

Alrighty,

So I got it to work pretty quickly. I grabbed a news tutorial from another site and tweaked it to display what I wanted (currently super ugly). I am still working on the few queries I would like to perform with this script. Also, could you be able to shed some light onto why I cannot change the sql query from

$query = "SELECT * FROM inventory ORDER BY Item_No ASC LIMIT $max_items";

to something like

$query = "SELECT * FROM inventory WHERE inv_id BETWEEN  3338 and 4123";

When I try to change the query it won't display any record. It's weird to me (maybe not to you). Other than that, here is my script that mostly works (does the job at least).

<?php

//Set the page title and include the HTML header.

$page_title = 'Inventory Database {-Inventory Display-}';
include('./includes/header.php');

// Database Connection 
include ('./includes/db.php'); 

?>

<table border="0" cellspacing="0" cellpadding="0" id="mm_bodytbl" align="center">
	<tr>
	  <td align="center">

<?php 
/* user config variables */
$max_items = 50; /* max number of news items to show */

function displayNews($all = 0) {
    /* bring in two variables
     * $db is our database connection
     * $max_items is the maximum number
     * of news items we want to display */
    global $db, $max_items;

/* query for news items */
if ($all == 0) {
    /* this query is for up to $max_items */
    $query = "SELECT * FROM inventory ORDER BY Item_No ASC LIMIT $max_items";
} else {
    /* this query will get all news */
    $query = "SELECT * FROM inventory ORDER BY Item_No ASC";
}
$result = mysql_query ($query);
while ($row = mysql_fetch_assoc ($result)) {
    /* display news in a simple table */
    echo "<br><table id=\"inv_display2\" cellspacing=\"0\" cellpadding=\"0\">\n";

    /* place table row data in 
     * easier to use variables.
     * Here we also make sure no
     * HTML tags, other than the
     * ones we want are displayed */
    $item = $row['Item_No'];        
    $description = $row['Description'];
    $location = $row['Location'];

    /* display the data */
			echo "<tr><td id=\"inv_idv_header\">Item Number</td></tr>\n";
    echo "<tr><td id=\"inv_idv_item\">$item</td></tr>\n";
			echo "<tr><td id=\"inv_idv_header2\">Description</td></tr>\n";
			echo "<tr><td id=\"inv_idv_description\">$description</td></tr>\n";
			echo "<tr><td id=\"inv_idv_header2\">Location</td></tr>\n";
    echo "<tr><td id=\"inv_idv_location\">$location</td></tr>\n";

    /* get number of comments */
    $comment_result = mysql_query ($comment_query);
    $comment_row = mysql_fetch_row($comment_result);

    /* display number of comments with link */
    echo "<tr><td id=\"inv_idv_footer\" align=\"right\" id=\"inv_btn_row\"><a href=\"{$_SERVER['PHP_SELF']}" .
         "?action=show&id={$row['inv_id']}\"><img src=\"images/buttons/button_expand.gif\" border=\"0\"></a>" . "</td></tr>\n";

    /* finish up table*/
    echo "</table>\n";
    echo "<br>\n";
}
}

function displayOneItem($id) {
    global $db;

/* query for item */
$query = "SELECT * FROM inventory WHERE inv_id=$id";
$result = mysql_query ($query);

/* if we get no results back, error out */
if (mysql_num_rows ($result) == 0) {
    echo "Didnt work\n";
    return;
}
$row = mysql_fetch_assoc($result);
echo "<TABLE border=\"1\" width=\"300\">\n";

/* easier to read variables and 
 * striping out tags */
$item = $row['item'];
$description = $row['description'] . '<a><b><i><u>';

/* display the items */
echo "<TR><TD><b>$item</b></TD></TR>\n";
echo "<TR><TD>$description</TD></TR>\n";

echo "</TABLE>\n";
echo "<BR>\n";

/* now show the comments */
displayComments($id);
}

function displayComments($id) {
    /* bring db connection variable into scope */
    global $db;

/* query for comments */
$query = "SELECT * FROM inv_details WHERE inventory_id=$id";
$result = mysql_query ($query);
echo "Item Description<BR>";

/* display the all the comments */
while ($row = mysql_fetch_assoc ($result)) {
    echo "<TABLE border=\"1\" width=\"300\">\n";

    $item = $row['item_no'];
    echo "<TR><TD><b>by: $item</b></TD></TR>\n";

    $description = $row['description'] . '<a><b><i><u>';
    echo "<TR><TD>$description</TD></TR>\n";

    echo "</TABLE>\n";
    echo "<BR>\n";
}     
}

/* this is where the script decides what do do */

echo "<CENTER>\n";
switch($_GET['action']) {

case 'show':
    displayOneItem($_GET['id']);
    break;
case 'all':
    displayNews(1);
    break;
default:
    displayNews();
}
echo "</CENTER>\n";
?> 

</td>
</tr>
</table>


<?php include ('./includes/footer.php') ?>

[dalecosp]

Aside from not knowing much about SQL (I have to look at the manual for most every query I write that isn't a real basic one, heh) I'd wonder if the numbers you're feeding to BETWEEN aren't typed in such a way that SQL can relate to them.

Congrats on getting it going, BTW. PHP is wonderful --- pretty good language to learn programming with, IMO.

[GIJew]

Okay, it is now up and running (again).

I ended up changing the script (very little) you gave me. It works for everything now and I don't have to change my pages at all (with the exception of a link on each page).

Thank you for your help/patience. I am now that much more informed. And for those of you who came upon this post let me tell you how it was solved. Thanks again for the help!

Insert what you want on this page from the DB, this is the code for the link.

echo "<a href=\"detail.php?id={$row['inv_id']}\">" . 'Expand' . '</a>';

Detail.php

<?php 
include('./includes/header.php');
include('./includes/db.php');
?>

<table id="bodytbl" align="center" cellpadding="0" cellspacing="0">
  <tr>
	  <td valign="top" align="center"><br>

<?
$id=@$_GET['id'];  // unless this web is external, GET is OK 

// get info from db 

$query = "SELECT * FROM inv_details WHERE id=$id";
$result = mysql_query ($query); 

//display info 
while ($row = @mysql_fetch_assoc ($result)) {
        echo "<table id=\"sub_container\" cellpadding=\"0\" cellspacing=\"0\"><tr><td id=\"sub_header\">\n";

    $item = $row['item_no'];
			$description = $row['description'];
			$location = $row['location'];
			$min = $row['min_stock'];
			$max = $row['max_stock'];
			$qty = $row['quantity'];
			$uom = $row['uom'];
			$vendor = $row['vendor_name'];
			$qty_used = $row['qty_used_last_year'];

			echo "<table cellpadding=\"0\" cellspacing=\"0\">";
    echo "<tr><td id=\"sub_txt_header\">$item</td></tr></table>\n";        
    echo "<tr><td id=\"sub_row1\" align=\"center\">$description</td></tr>\n";
			echo "<tr><td id=\"sub_row2\"><b>Location:</b> $location</td></tr>\n"; 
			echo "<tr><td id=\"sub_row1\"><b>Minimum Stock Level:</b> $min</td></tr>\n"; 
			echo "<tr><td id=\"sub_row2\"><b>Maximum Stock Level:</b> $max</td></tr>\n"; 
			echo "<tr><td id=\"sub_row1\"><b>Quantity on Hand:</b> $qty</td></tr>\n"; 
			echo "<tr><td id=\"sub_row2\"><b>Unit of Measure:</b> $uom</td></tr>\n"; 
			echo "<tr><td id=\"sub_row1\"><b>Vendor Name:</b> $vendor</td></tr>\n"; 
			echo "<tr><td id=\"sub_row2\"><b>Quantity Used Last Year:</b> $qty_used</td></tr>\n";
			echo "<tr><td align=\"center\" id=\"sub_img_row\"><span class=\"sub_img\"><img src=\"images/no-image.gif\" alt=\"$item\"></span></td></tr>\n"; 
			echo "<tr><td align=\"left\" id=\"sub_btn_row\"><input type=\"image\" onClick=\"history.go(-1)\" src=\"images/buttons/button_back.gif\" alt=\" Go Back\"></td></tr>\n";
    echo "</td></tr></table>\n";
    echo "<BR>\n";
}     
?>
    </td>
	</tr>
</table>
include('./includes/footer.php');
?>