Displaying a table data uniquely for variable total rows

[jmarco]

Does that title make sense?

Here's the deal...

I have a table with X#-Rows...

Say I have 5-Fields per Row...

I'm displaying a list to the browser so a user can pick from a list of options displayed by Field-1 -> clicking on Field-1 will run a script2 that shows Field-1 through Field-5 of that Row.

/*SCRIPT 1*/
$query = "SELECT record1 FROM table";
$result = mysql_query($query);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($line as $field1) {
        print "<td><a href=\"url/script2.php?find=$field1">$field1</a></td>";
    }
}

So say I have displayed as a list of Record-1's:
Cat
Dog
Dog
Chicken

The user can click on Cat and get details about that Record by running url/script2.php?find=Cat because script2's query look like:

/*SCRIPT 2*/
$find=addslashes($find);
"SELECT * FROM table WHERE field1=\"$find\"";

That's fine... I can do that...

Here's my delima

What if the user clicks on Dog?

If the user clicks on Dog, it will display the results for both Dog records because the 2nd 'SELECT' statement will pull out the Record where ever Field-1 = $find...

But I don't want that. Here's what I want... if the user clicks on the First Dog, I want it to display only that Dog and NOT the 2nd Dog.

Now for me that means all I have to do if find another field that is unique to ALL Records like "date-of-birth' (DO😎. Then I run a query that finds both the Record-1 and the DOB (say Record-4) so that when I run 'url/script2.php?find=$record1&where=record4' I can say in 'script2.php':

/*SCRIPT 2*/
$find=addslashes($find);
$where=addslashes($where);
"SELECT * FROM table WHERE record1=\"$find\" AND record2=\"$where\"";

That is what I want to do...

But I can't seem to get field1 and field4 seperated from each other inside the 'while', and 'foreach' loops where the link to script2 is:

/*SCRIPT 1*/
$query = "SELECT field1, field4 FROM table";
$result = mysql_query($query);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($line as $field) {
        print "<a href=\"url/script2.php?find=$field">$field</a>";
    }
}

This loop will display both field1 and field4 as a link... I just want record1 as the link and field4 as plain text but still tied to record1 so that I can find field1 where field4 exists.

This what I thought I wanted:

/*SCRIPT 1*/
$query = "SELECT field1, field4 FROM table";
$result = mysql_query($query);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
    foreach ($line as $field) {
        print "<a href=\"url/script2.php?find=$field[\"field1\"]&where=$field[\"field4\"]\">$field[\"field1\"]</a>";
    }
}

/*SCRIPT 2*/
$find=addslashes($find);
$where=addslashes($where);
"SELECT * FROM table WHERE record1=\"$find\" AND record4=\"$where\"";

This doesn't seem to be the solution... is there one?

Any help would be GREATLY appreciated...

One problem I see is that at the end of the url where the variables are being declared: 'field[\"record1\"]'... record1 isn't a string... how would I get record1 and record4 into a string to say: 'field[\"$record1\"]'... or does that even solve my problem?

[ahundiak]

Your loop is not quite right. Try
while($line = mysql_fetch_array($result,MYSQL_ASSOC))
{
$field1 = $line['field1'];
$field4 = $line['field4'];
// And build the link with $field1 and $field2
}

[T_E_]

Using two non-unique fields to identify a record is not any more valid way than using just one. This just makes it a bit more improbable to have two unidentifiable records in same query, but it is still possible.

What you need to do, is include a real primary key in your database. Simplest way is to add an integer (bigint) field to your table, setting it to auto_increment and making it primary key. After this, you make your queries like:

SELECT id, field1 FROM table

and refer to the results always by its unique key - the id column in this example. Like this:

/*SCRIPT 1*/
$query = "SELECT id, field1 FROM table";
$result = mysql_query($query);
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
   $id = $line['id'];
   $field1 = $line['field1'];
   print "<td><a href=\"url/script2.php?find=$id\">$field1</a></td>";
}

/*SCRIPT 2*/
$id=addslashes($id);
$query = "SELECT * FROM table WHERE id=$id";
$result = mysql_query($query);
// etc...

That way you don't have to worry about identifying with two or more fields and you can be certain that there are no duplicates in your tables.

[jmarco]

Thanks guys.

That taught me quite a bit actually.

still learning,
marco