Page 1 of 1

Select a record that is a result of a query

Posted: Tue Jul 26, 2011 2:02 am
by crowegreg
Hello, On a page I have two fields, first name and last name, based on what the user enters, I execute a query to display the records within this recordset. Is it possible for the user to select one of the displayed records of the recordset, and use the selection for the creation of another query?

Re: Select a record that is a result of a query

Posted: Tue Jul 26, 2011 2:09 am
by Benjamin
Yes.

But you need to be a little more clear on what you are trying to accomplish. What data will you be using from the selected record? What exactly do you want to accomplish when you say "creation of another query"?

Re: Select a record that is a result of a query

Posted: Tue Jul 26, 2011 2:47 am
by crowegreg
I didn't do a good job of explaining my needs. Here's a better attempt.
On the first page their are two fields, first name and last name. The user can enter any part of either or both of these fields. I then execute a query based on what they've entered. On the second page I will display all the records that match their criteria. The fields I'm displaying are customerid, firstname, and lastname. So if the recordset contains 5 records, I'd like for them to be able to select or click on one of the records, then I will execute a query using the customerid to retrieve all the datafields for that particular record and display on another page. Hope this makes it a little more clearer.

I'm at the point right now where I'm displaying the records that match what the user has selected. I don't know how they can then select 1 of those records.

Thanks for your assistance!!!

Re: Select a record that is a result of a query

Posted: Tue Jul 26, 2011 3:05 am
by Benjamin
crowegreg wrote:The fields I'm displaying are customerid, firstname, and lastname.
So you want to also pull the ID (Primary Key) for those records. You can then use the id in a GET requested to pull all the fields for that row using another query.

Code: Select all

<a href="details.php?id=<?php echo $row['id']; ?>"></a>

Code: Select all

# id will be in $_GET['id'];

# verify id is a real number

# use it in the database query

$query = "SELECT fields FROM table WHERE id = '" . $db->escape($_GET['id']) . "'";

Re: Select a record that is a result of a query

Posted: Tue Jul 26, 2011 3:16 am
by crowegreg
I'm a little lost. I don't know how to incorporate the suggestion you've made. Here's my code on the second page displaying the results of the first query:

Code: Select all

	<table cols="3" width="800px">
	<th>CustomerID</th>
	<th>First Name</th>
	<th>Last Name</th>
						
<?php
	if(mysql_num_rows($result) > 0) {
		while($row = mysql_fetch_array($result)) {
			echo "<tr>";
			echo "<td>" . $row['0'] . "</td><td>" . $row['1'] . "</td><td>" . $row['2'] . "</td>";
			echo "</tr>";
		}		
	}

Re: Select a record that is a result of a query

Posted: Tue Jul 26, 2011 3:21 am
by Benjamin
Rather than write the code for you, I'll give you hints.
  • You're using mysql_fetch_array instead of mysql_fetch_assoc. This is making things harder for you because you're looking at meaningless numbers instead of being able to use the field names as the array keys.
  • You're already outputting the id, first name and last name. You probably want at least one of those to be clickable. So you'll want to turn one into a link.
  • PHP.net is your friend. It wouldn't hurt to spend some time reading and learning how to use the manual. It is your best friend.

Re: Select a record that is a result of a query

Posted: Tue Jul 26, 2011 3:25 am
by crowegreg
I'll start reading tomorrow. Thanks for your assistance!!

Re: Select a record that is a result of a query

Posted: Tue Aug 02, 2011 7:57 pm
by SabirAhmed
I'd probably get told theres a much better way to do this but heres a start:

Basically, you echo out the data, e.g:

echo "<td>" . $row['0'] . "</td><td>" . $row['1'] . "</td><td>" . $row['2'] . "</td>";

But you wrap one piece of data in a link (i am assuming row ['0'] is the id row) :

echo "<td><a href:'www.website.com/viewdetailspage?pid=$row['0']'" . $row['0'] . "</a></td><td>" . $row['1'] . "</td><td>" . $row['2'] . "</td>";

Then in your php coding on the page which you plan to display indivual peoples details, in the mysql query you put something like:

$TargetID = $_GET['pid']; -> This allows the page to get the id it should be looking for from page url

mySQL_query= "Select * where 'id' = $TargetID";

And just echo out the data again for this particular item on the database which you have identified.

Re: Select a record that is a result of a query

Posted: Thu Aug 04, 2011 7:00 am
by genix2011
Hi,

why are you using chars to select an array index?
You can just write $row[0] instead of $row['0'], also as Benjamin already pointed out it's better to just use mysql_fetch_assoc for the code to be more readable or if you want to work with indexes mysql_fetch_row.

mysql_fetch_array is only good if you really need both, indexed and assoc.