Linking to all row data from one input field

Forum Moderators: coopster

Message Too Old, No Replies

Linking to all row data from one input field

Sub_Seven

12:32 am on Jul 27, 2010 (gmt 0)

Hello everybody,

Hope I can be clear here, this is my question:

I have a mysql DB with one table that feeds from a form, I created a php page to retrieve data of off it using php select, this page I just created only retrieves a few fields, one of them is the person's name, how do I go about making the person's name a link that will display ALL the data in that row in a different php page without making this manually as new rows are created?

I've searched and searched with no luck, so if someone here can give me a hand it would be greatly appreciated, thanks :)

Matthew1980

6:57 am on Jul 27, 2010 (gmt 0)

Hi there Sub_Seven,

Could you post the relevant code so that we can suggest improvements or help with the construction/logic process?

Cheers,
MRb

Sub_Seven

12:22 pm on Jul 27, 2010 (gmt 0)

Sure, here its my php select page:


<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<link rel="stylesheet" type="text/css" href="css/select.css" />
</head>
<body>
<?php
$con = mysql_connect("localhost","user","pass");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("application", $con);

$result = mysql_query("SELECT * FROM applications");
?>

<div id="content">
<img src="images/eialogo.jpg" />
<div class="header">Name</div>
<div class="header">Surname</div>
<div class="header">Application date</div>
<div class="header">Date of birth</div>
<div class="header">Phone number</div>
<?php
while($row = mysql_fetch_array($result))
{
echo "<div class='input'>";
echo $row['first_name'];
echo "</div>";
echo "<div class='input'>";
echo $row['surname'];
echo "</div>";
echo "<div class='input'>";
echo $row['date_application'];
echo "</div>";
echo "<div class='input'>";
echo $row['date_of_birth'];
echo "</div>";
echo "<div class='input'>";
echo $row['phone_1'];
echo "</div>";
echo "<br />";
}
?>

<div id="clearfooter"></div>
</div>
<div id="footer"></div>

<?php
mysql_close($con);
?>
</body>
</html>

As you can see, there are 5 entries (name, surname, Application date, dob, and phone number) but there is a lot more data per person, again, my question is, how can I make the person's name (for example) clickable so that it can take the user to a page where all data from that row/person can be displayed?

I know how to make this happen manually, I'm just looking for a method that is more efficient and I'm pretty sure this can be achieved with php, but sadly I'm a complete newbie :(

Thanks...

Matthew1980

3:11 pm on Jul 27, 2010 (gmt 0)

Hi there Sub_Seven,

Well making the name clickable is the easy part ;)

The Link
while($row = mysql_fetch_array($result)){
//You can break out of php and into html for ease of editing :)
?>
<div class="input">
<a href="YourNewPage.php?FirstName=<?php echo $row['first_name'];?>">
<?php echo $row['first_name']; ?>
</a>
</div>
..
More entries
<?php
//close while loop
}
?>

Then on a new file called (for this example) YourNewPage.php and do this in it:-

The receiving page/script
<?php
if(isset($_GET['FirstName']) && !empty($_GET['FirstName'])){

//captured the data now display contents from db
$SqlQuery = "SELECT * FROM `yourtable` WHERE `first_name` = '".strip_tags(mysql_real_escape_string($_GET['FirstName']))."' ";

$SqlSent = mysql_query($SqlQuery) or die(mysql_error());

//loop through returned data
while($result = mysql_fetch_array($SqlSent)){
//display data here according to the column names etc
//I have just done this to prove that there is data being returned from the query
//once you have the desired data here, format it in HTML as you did before
echo "<pre>";
print_r($result);
echo "</pre>";
}//close while loop

}
else{
//redirect back to form as $_GET doesn't have a value
header("location: yourHtmlPage.php");
exit;
}
?>

Please bear in mind that this is typed on the fly and may contain errors, so effectively pseudo code, but you should get the idea for the logic involved there anyway.

Hope that helps ;)

Cheers,
MRb

rocknbil

6:13 pm on Jul 27, 2010 (gmt 0)

You should probably change this

<?php echo $row['first_name'];?>

to this

<?php echo rawurlencode($row['first_name']);?>

And apply rawurldecode to it before searching.

The possibility exists they may have spaces or invalid URL characters in the textual value of "first name."

Matthew1980

6:44 pm on Jul 27, 2010 (gmt 0)

Hi all,

Ah yes, that's a good point. I rarely use rawurlencode($data); I usually just do a str_replace(" ", "_", $row['first_name']) purely for that reason - but that's just my preference.

Cheers,
MRb

Sub_Seven

6:53 pm on Jul 27, 2010 (gmt 0)

Thanks so much Matthew1980,

The code works great, I can get all the data by clicking on the person's name, there is only one thing I can't seem to get done, you said


//once you have the desired data here, format it in HTML as you did before 
echo "<pre>"; 
print_r($result);
echo "</pre>";

I'm assuming html formatting has to be inserted in between those tags, but the method I did before is not working for me now, for example, how can I format the first name to look different from the formatting that the date of birth will have?

On another note (doubt...?), as it is right now the code is displaying data like this:

Array
(
[0] => 495
[app_id] => 495
[1] => 2010-07-26
[date_application] => 2010-07-26
[2] => Someone
[first_name] => Someone
[3] =>
[middle_name] =>
[4] => Somelname
[surname] => Somelname
[5] => Married
[marital_status] => Married
[6] => ... All the way to input #177
[marital_status] => ... All the way to input #177...
)

As you can see every entry is seen twice, one with [#] and the other one with the name of the field in the DB, how can I prevent the code from displaying:

1. The duplicate line with [#]
2. The name of the field in the DB

This is so I can customize the looks of the result page...

I know I'm asking too much, and believe me, every time I ask I've done a lot of research before giving up and asking, so all the help is extremely appreciated, thanks so much.

rocknbil

2:07 am on Jul 28, 2010 (gmt 0)

I usually just do a str_replace(" ", "_", $row['first_name']) purely for that reason - but that's just my preference.

I used to too. Until the big dogs on this site beat me senseless and made me start using dashes for URL's instead. :-) They index better and don't get stashed down by the link underlines.

URL encoding is important for things like ', ", (, and many other unexpected ones.

Matthew1980

7:27 am on Jul 28, 2010 (gmt 0)

^^
Hi there Rocknbil,

That's weird, I wonder why they index better? The only reason I can imagine is because the _ get 'obsucured' if there is css styling involved, so I can see the point of using hyphens that way, but if rawurlencode()->rawurdecode() does the job for me, I might just do that instead, but old habits etc. Also just depends on how I construct the URL in the link in the first place ;-p

@Sub_Seven:

echo "<pre>";
print_r($result);
echo "</pre>";

This code I just used as a method of debug - it formats the returned result array ($result) from mysql_fetch_array() in human readable form - from the output you can see that the entries within the database have been collected & are ready to be displayed.

Formatting

//loop through returned data
while($result = mysql_fetch_array($SqlSent)){
//break out of php into html for easier editing:)
?>
Your first name is:<?php echo $result['first_name'];?>
Your Surname is:<?php echo $result['surname'];?>
Date Applied:<?php echo $result['date_application'];?>
<?php
//break back in to close the loop :)
}//close while loop

Be cautious as you have duplicate columns there, you have two 'marital_status' - unless that's intended, but I doubt that - this will confuse the mysql_query if you asked for just marital_status...

But hopefully from that example you can see how you structure that output, the while loop will output to the browser until there are no more results sets left.

Hope that makes sense,

Don't forget though, we all have to learn sometime or other, doesn't matter if you ask a few times en route to success - so long as you get there, above all - Have Fun! :)

Cheers,
MRb

rocknbil

3:36 pm on Jul 28, 2010 (gmt 0)

That's weird, I wonder why they index better?

I forget why (which is one of the disadvantages of getting old, forgetting more than many people will ever know) but it's well discussed here, and explored by Matt Cutts, try a G search for underscore site:webmasterworld.com. Underscores DO index fine, I have sites to prove that, but the dash is preferred by S.E.'s for many reasons. Every little bit you can get . . .

Sub_Seven

11:35 pm on Jul 28, 2010 (gmt 0)

Hey guys, thanks so much for everything...

I do remember seeing a video of Matt Cutts saying that it is better to use - instead of _ for url naming but I also remember him not specifying why it was better, all I know is that since then I started using hyphens... oh well!

@Matthew1980

Thanks for the hint, it works great (still gotta check about that marital status field being there twice), other than that I got it running smoothly, I guess I'm used to formatting things I can call from an HTML tag a div or class and this php syntax is kicking my behind and it will go on until I get tons of practice on the matter, by the way, what would you say is a really good source for learning, I use w3schools a lot to read about different things but I could be missing out some other good websites...

@rocknbil, thanks for the rawurlencode hint, I've made the change and that will save me some headaches in the future as I didn't know that this was a vulnerability.

See you around! Thanks!

Matthew1980

7:07 am on Jul 29, 2010 (gmt 0)

Hi there sub_seven,

Glad your getting there anyway.

by the way, what would you say is a really good source for learning, I use w3schools a lot to read about different things but I could be missing out some other good websites...

Three words: Practice, Practice & practise some more ;)

W3CSchools is a good resource, as is php.net and the library on here ;) - but generally if you have a localhost installation and a project in mind (simple to start) that would be about the best, though different people learn at different paces - it's your choice. Have fun with it.

Cheers,
MRb

Sub_Seven

4:18 pm on Jul 29, 2010 (gmt 0)

Believe you me, practice has been the best tool ever, all the way from html, css, javascript and now php.

Thanks a lot guys, see you in my next question :)