building a page from a database

[fat creative]

I have a form with drop downs that will return a listing of items depending on what the user selected in the drop downs.  From the list of items returned, I'd like to be able to click on an item and then build a detailed page about that item, including other fields that were not displayed originally.  I am just learning PHP and could use some direction.  I have test pages and data at www.fatcreative.com/test.html.  Right now it's just selecting names from a database, so the idea would be that once the names are returned, you could click on any name and then a new page would come up listing a lot more detailed information about that person.  I will eventually have over 100 items in my database and I was hoping to avoid having to build 100 separate pages.  I'm guessing there's a way to do this and then I'd only have to build one script :-).  If anyone could steer me in the right direction, I'd be ever so grateful. I am loving learning PHP!

 

This is the code from my first PHP statments:

$query = "SELECT * FROM phoneList";

$result = mysql_query($query)

    or die ("Couldnt execute query");

 

 

 

//this will list what customer entered/selelcted on form

  echo "You made the following selections <br>";

  foreach ($_POST as $field => $value)

  {

    echo "$field = $value<br>";

  }

 

 

 

# GRAB THE VARIABLES FROM THE FORM

$Lastname = $_POST['Lastname'];

$Firstname = $_POST['Firstname'];

 

 

//this should select based on form selections

$query = "SELECT * FROM phoneList

              WHERE ID > 0 ";

 

if (strlen($Firstname) > 0) {

  $query .= "AND Firstname='$Firstname'";

}

 

if (strlen($Lastname) > 0) {

  $query .= "AND Lastname='$Lastname'";

}

 

$result = mysql_query($query)

    or die ("Couldnt execute query");

 

$numrows = mysql_num_rows($result); //count the number of rows returned

if($numrows < 1 ) //if none!!!

{

echo ("There were no rows returned from the database");

}

else //otherwise draw the table

//put info on new lines

echo "<table cellspacing='15'>";

echo "<tr><td colspan='3'></td></tr>";

while($row = mysql_fetch_assoc($result))

{

extract($row);

echo "

<tr>\n

<td>$Firstname</td>\n

<td>$Lastname</td>\n

<tr>

<td>$Email</td>\n

<tr>

<td>$Phone</td>\n

</tr>\n";

echo "<tr><td colspan='3'></td></tr>\n";

}

echo "</table>\n";

 

[Orionsbelter]

Are you just wanting to list that users details? if so then yes you can. and also on your drop down are typing the names in because you don't have too you can just get all the names for that table and it'll evho them all.

[Orionsbelter]

change $query = "SELECT * FROM phoneList WHERE username='$username'"; that will get the usernames details that you just seleted on the drop down.

[Psycho]

OK, let's say you have a page called "showdetails.php" that will act as you display page. On your listing page when you create the links, create the links something like this:

 

<a href="showdetails.php?id=5">Bob</a>

Where 5 is the ID for the person Bob.

 

Then on the "showdetails.php" page you will access the ID through $_GET['id'] and then just query for that users data from the database and display it however you want.

 

Example

$userID = (int) $_GET['id'];
$query = "SELECT * FROM users WHERE id = $userID";
$result = mysql_query($query) or die (mysql_error()."<br />Query: $query");

if (mysql_num_rows($result)==0) {
    echo "No user found for id '$userID'";
} else {
    $user = mysql_fetch_assoc($result);
    echo "Name: {$user['name']}<br />";
    echo "Address: {$user['address']}<br />";
    //etc...
}

[Orionsbelter]

yeah thats a good way although i'd  do

$username = $_GET['username'];
$query = "SELECT * FROM users WHERE username = $username";
$result = mysql_query($query) or die (mysql_error()."<br />Query: $query");

if (mysql_num_rows($result)==0) {
    echo "No user found for the username $username'";
} else {
    $user = mysql_fetch_assoc($result);
    echo "Name: {$user['name']}<br />";
    echo "Address: {$user['address']}<br />";
    //etc...
}

[Psycho]

Passing username is not a good idea IMHO. Because username may contain many different characters that don't work and play well when included in a URL. Since he is create a list page of "known" records, the error handling would only kickin if 1) a record was deleted after the user generated the list page and before they clicked the link or 2) the user manually modifies the URL to include an invalid id. Both of which would rarely happen.

 

Also, the OP only stated that the records had a firstname and lastname. This could be a database for a mailing list or contacts. I did not make the assumption that there is even a username. In any case, the whole point of having a unique record ID is to use it for instances such as this.

 

For example, look at the URL of this page and you will see "topic,196946". That is the ID for the record of this topic. You wouldn't want to pass the title of the topic as the parameter. If anything, I would agree that the error handling could be modified to produce differenet, targeted messages. E.g.; No user ID submitted, unable to find user record, etc.

[fat creative]

mjdamato, thank you very much. this is exactly what I want.  I am just having a bit of trouble figuring out how to call the id.  each record returned will obviously have its own unique id, so i can't enter id=5.  i need to use the id variable, right? but i've tried a 1,000 different ways to get the variable from the database.  This is my code so far.  I'm not sure if the top part is relevant, but the part in question is at the bottom.  Thank you soooo much, everyone, for all your help. the form is at www.fatcreative.com/test.html and the code is:

 

# GRAB THE VARIABLES FROM THE FORM

$Lastname = $_POST['Lastname'];

$Firstname = $_POST['Firstname'];

$ID = $_POST['id'];

 

//this should select based on form selections

$query = "SELECT * FROM phoneList

              WHERE ID > 0 ";

 

if (strlen($Firstname) > 0) {

  $query .= "AND Firstname='$Firstname'";

}

 

if (strlen($Lastname) > 0) {

  $query .= "AND Lastname='$Lastname'";

}

 

$result = mysql_query($query)

    or die ("Couldnt execute query");

 

$numrows = mysql_num_rows($result); //count the number of rows returned

if($numrows < 1 ) //if none!!!

{

echo ("There were no rows returned from the database");

}

else //otherwise draw the table

//put info on new lines

echo "<table cellspacing='15'>";

echo "<tr><td colspan='3'></td></tr>";

while($row = mysql_fetch_assoc($result))

{

extract($row);

echo "

<tr>\n

<td>$Firstname</td>\n

<td>$Lastname</td>\n

<tr>

<td>$Email \n</td>

<tr>

<td>$Phone <a href='showdetails.php?id=$id'>View Details </a></td>\n

</tr>\n";

}

echo "</table>\n";

 

[Psycho]

OK, a few comments first.

 

1. The third line where you set $ID makes no sense. ID is not one of the values posted and you don't use it anyway.

 

2. You need to validate/clean ALL values from user input - especially when runing a query on it. Else, someone could use SQL injectyion to do nasty things to your data.

 

3. You have some invalid HTML that is being built. You first build a tablerow with a 3 column cell, then the first data row has two single cells and then a third with just one single cell???

 

4. Perhaps the reason the ID is not getting displayed is due to the 'case' of the variable name. What is the exact name of your id field in the database: Id, id, ID? To see what data is actually being returned try adding "print_r($row)" within your while loop.

 

Here is some modified code to fix issues 1-3. I can't do much about #4 without knowing exactly what your db structure is.

 

<?php

# GRAB THE VARIABLES FROM THE FORM
$Lastname = mysql_real_escape_string($_POST['Lastname']);
$Firstname = mysql_real_escape_string($_POST['Firstname']);

//this should select based on form selections
$query = "SELECT * FROM phoneList WHERE ID > 0";

if (strlen($Firstname) > 0) {
  $query .= " AND Firstname='$Firstname'";
}
           
if (strlen($Lastname) > 0) {
  $query .= " AND Lastname='$Lastname'";
}

$result = mysql_query($query)
     or die ("There was an error:<br />".mysql_error()."<br />Query: $query"); 

if(!mysql_num_rows($result)) //if none!!!
{   
   echo "There were no rows returned from the database";
}
else //otherwise draw the table 
{
   //put info on new lines
   echo "<table cellspacing=\"15\">\n";
   while($row = mysql_fetch_assoc($result))
   {
      extract($row);
      echo "<tr><td>";
      echo "$Firstname $Lastname<br />";
      echo "$Email<br />";
      echo "$Phone<br />";
      echo "<a href=\"showdetails.php?id=$id\">View Details</a>";
      echo "</td></tr>\n";
   }
}
echo "</table>\n";

?>

[fat creative]

mjdamato - I'm sure you hear this quite a bit, but thank you thank you thank you. 

1. That 3rd line with the ID was me trying to figure out how to get this to work. I didn't think I needed it, but I was trying anything.

2. I don't know what SQL injection is (I've been teaching myself PHP the last few weeks) but I will look it up. Thank you for the advice.

3. I thought it looked strange too, it is something I copied / pasted from someone. I've also been mucking around and may have messed some of it up.

4. It was the wrong "case". Being a newbie, I just didn't see it, but I've fixed it now and it's working like a charm.

 

I am just so thrilled that this thing is working!  Thank you again so very much for your help.

 

Jeanette

 

[Psycho]

SQL injection is where someone enters code into an input field and then when the page is posted (if the processing page does not prevent it) the code will be run in the query.

 

For example if you had an authorization script such as this:

SELECT * FROM users WHERE uname = '$username' and pword = '$password'

 

Now, let's assume the user entered the values "bob" and "a' or 1 = 1". That would result in this query being run

SELECT * FROM users WHERE uname = 'bob' and pword = 'a' or 1 = 1

 

Since 1 always equals 1 it will select ALL the records in the database. Then the code would most likely grab the first erecord (assuming there would have been only 1 match) and authenticate against that user. In most cases the first user account is an admin.

 

By using mysql_real_escape_strings() the text is "escaped" so it will not be interpreted. In the example above, instead of the password being interpreted as code it would be trated as a literal string. I.e. the query would look for a password of "a' or 1 = 1"

[fat creative]

Thank you (again) for the information.  I think I understand. For future reference, do I have to worry about SQL injection on a form that is just drop downs? Or is it just for areas where the user is entering something? I think it's the latter, but I just want to make sure.

 

I am trying to build the showdetails.php page and I am having trouble getting the variable to work.  My code is this:

$ID = $_GET['id'];

 

echo "id equals $ID <br />"; // to see if its passing the right variable

 

$query = "SELECT * FROM phoneList

  WHERE ID= \"$ID\" ";

 

echo " my firstname is $Firstname <br />";

echo " my lastname is $Lastname";

 

So I can see that the right variable is being passed through to the new page, but I can't seem to get it to query on the database.  I've tried several different combinations of WHERE ID=$ID, WHERE ID="$ID" and probably a few more I just can't remember at this point. The Firstname and Lastname fields are appearing as blank. I've double checked that the variable names have the right case this time.

The form process starts at www.FatCreative.com/test.html

 

Thank you in advance for ANY suggestions!!!!

[Psycho]

Thank you (again) for the information.  I think I understand. For future reference, do I have to worry about SQL injection on a form that is just drop downs? Or is it just for areas where the user is entering something? I think it's the latter, but I just want to make sure.

 

It is ALL user input. It makes no difference if it is a text field, a select list, a radio group, etc. Or even data sent on the URL (i.e. $_GET values). A user can create an "offline" form and post it to your page. Never, ever trust any data submitted from a user.

 

 

 

You don't state if $ID is getting set properly. Since you don't state that that echo is not displaying the value I will assume it is getting set properly. Also, I don't see where you are running the query. Do you have any error handling when running the query? Try this:

 

<?php

$ID = $_GET['id'];

echo "Submitted id [{$ID}]<br>";

$query = "SELECT * FROM phoneList WHERE ID= '$ID' ";
$result = mysql_query($query) or die (mysql_error()."<br>Query: $query");

$user = mysql_fetch_assoc($result);
echo "<br>My firstname is " . $user['Firstname'];
echo "<br>My lastname is " . $user['Lastname'];

?>

 

If this does not work, what output do you get?

[fat creative]

This worked wonderfully. Again, THANK YOU THANK YOU THANK YOU.  This whole process shows me there is so very much to learn.  I am looking into taking an online class at one of the local colleges.  I'll be making a donation to phpfreaks later today too!  Thank you again for everything!

 

Jeanette