Clickable MySQL database results

@strac72Jun 28.2010

Hi All,

I am totally new to php and, although I have managed to make a sucessful feedback and search/view_search page, I have no idea how to make the returned results link back to the relevant html pages. The results are just static and uselessif they cannot link to a page. If you want to look at the page concerned, the url is: ~~[url]~~http://www.stracwebdesign.co.uk/mannerings/pages/search.php[/url]

I am a student and this is my project, any help would be welcome!
I realise this question has been asked many times across various forums but I can’t make sense of the replies.

to post a comment

PHP

11 Comments(s) _↴

@OctoberWindJun 28.2010 — #what do you need clickable, and where does it need to click to?

Just build your link structure when you output this line of code (assuming this is what you need clickable):

[code=php]
 <tr>
 <td align="left">22</td>
 <td align="left">Denmark</td>
 <td align="left">Detached</td>
 <td align="left">4</td>
 <td align="left">999.999</td>
 <td align="left">Yes</td>
 
   <td align="left">Double</td>
 
  </tr>
 [/code]

@OctoberWindJun 28.2010 — #Additionally, I think you need to take a second look at your code. it appears that you're loading a full html page [i]inside[/i] your page, because you've got two DOCTYPES, Head tags, body tags, ect

(view source from Google Chrome):

[code=php]<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
 <html xmlns="http://www.w3.org/1999/xhtml"> 
 <head> 
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
 <title>Manning & Davidson - Estates of Distinction</title> 
 <link href="../css/manning&amp;davidson.css" rel="stylesheet" type="text/css" /> 
 
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
 <html xmlns="http://www.w3.org/1999/xhtml"> 
 <head> 
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
 <title></title> 
 
 </style> 
 <script language="JavaScript1.2" type="text/javascript" src="mm_css_menu.js"></script> 
 <script src="../Scripts/swfobject_modified.js" type="text/javascript"></script> 
 <link href="../css/manning&amp;davidson.css" rel="stylesheet" type="text/css" /> 
 </head> 
 
 <body> 
 <div align="center"> 
 <object classid="clsid:D27CDB6E-AE6D-11cf-96B8-444553540000" width="800" height="125" align="absmiddle" id="FlashID" title="Manning &amp; Davidson logo"> 
 <param name="movie" value="../includes/nonmusiclogo.swf" /> 
 <param name="quality" value="high" /> 
 <param name="wmode" value="opaque" /> 
 <param name="swfversion" value="8.0.35.0" /> 
 <!-- This param tag prompts users with Flash Player 6.0 r65 and higher to download the latest version of Flash Player. Delete it if you don&#8217;t want users to see the prompt. --> 
 <param name="expressinstall" value="../Scripts/expressInstall.swf" /> 
 <!-- Next object tag is for non-IE browsers. So hide it from IE using IECC. --> 
 <!--[if !IE]>--> 
 <object data="musiclogo.swf" type="application/x-shockwave-flash" width="800" height="125" align="absmiddle"> 
 <!--<![endif]--> 
 <param name="quality" value="high" /> 
 <param name="wmode" value="opaque" /> 
 <param name="swfversion" value="8.0.35.0" /> 
 <param name="expressinstall" value="../Scripts/expressInstall.swf" /> 
 <!-- The browser displays the following alternative content for users with Flash Player 6.0 and older. --> 
 <div> 
 <h4>Content on this page requires a newer version of Adobe Flash Player unless you are using a 64 bit browser. Adobe currently offer no support for 64 bit browsers.</h4> 
 <p><a href="http://www.adobe.com/go/getflashplayer"><img src="http://www.adobe.com/images/shared/download_buttons/get_flash_player.gif" alt="Get Adobe Flash player" width="112" height="33" /></a></p> 
 </div> 
 <!--[if !IE]>--> 
 </object> 
 <!--<![endif]--> 
 </object> 
 </div> 
 &nbsp;
 
 </div> 
 <p>&nbsp;</p> 
 <script type="text/javascript"> 
 swfobject.registerObject("FlashID");
 </script> 
 </body> 
 </html> 
 <link href="../css/manning&amp;davidson.css" rel="stylesheet" type="text/css" /> 
 
 
 <h1 id="mainhead">Search For Houses</h1> 
 
 
 
 <h5>View Houses for Sale by Country</h5> 
 
 <div align="center"> 
 
 <form action="view_search.php" method="post"> 
 <select  name="country"> 
 <option value="null">Please select a country</option> 
 <option value="Denmark">Denmark</option> 
 <option value="France">France</option> 
 <option value="Germany">Germany</option> 
 <option value="Iceland">Iceland</option> 
 <option value="Iraq">Iraq</option> 
 <option value="Mexico">Mexico</option> 
 <option value="Portugal">Portugal</option> 
 <option value="Saudi Arabia">Saudi Arabia</option> 
 <option value="South Africa">South Africa</option> 
 <option value="Spain">Spain</option> 
 <option value="Sweden">Sweden</option> 
 <option value="United Kingdom">United Kindom</option> 
 <option value="United States">United States</option> 
 
 </select> 
 <input type="submit" name="submit" value="Search" /> 
 
 <input type="hidden" name="submitted" value="TRUE" /> 
 
 </form> 
 
 </div> 
 
 <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> 
 <html xmlns="http://www.w3.org/1999/xhtml"> 
 <head> 
 <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> 
 
 <title></title> 
 
 <link href="../css/manning&amp;davidson.css" rel="stylesheet" type="text/css" /> 
 
 </head> 
 
 <body> 
 <div id ="footer"> 
 <div align="center">&copy; Copyright 2010- <a href="http://www.stracwebdesign.co.uk">Stracwebdesign.co.uk</a> [Site last updated by Paul Strachan on 25/06/2010]
 <p></p></div> 
 
  <div align="center"> 
 
  <a href="mailto:[email protected]">Please click here to email us with any queries you may have.</a> 
  </div> 
  &nbsp;
 
 <div align="center"><div id ="footer"></div> 
 <div align="center"><a href="index.php">Home</a>||<a href="detached.php">Detached</a>||<a href="semi.php">Semi Detached</a>||<a href="terraced.php">Terraced</a>||<a href="search.php">Search</a>||<a href="feedback.php">Feedback</a></p><p></p></div> 
 [/code]

@iahneJun 28.2010 — #mysql_connect('localhost','root','1234') or die("Unable to connect to MySQL");

mysql_select_db(dbname) or die("Unable to select a database");

$query="select * from tablename where column=1";

$result = mysql_query($query) or die("Error in query");

if(mysql_num_rows($result)>0)

{

echo '<table cellpadding="1" cellspacing="1" border="1">';

echo '<th>Header 1</th>';

echo '<th>Header 2</th>';

while($row=mysql_fetch_row($result))

{

echo '<td><a href="link1.php">'.$row[0].'</a></td>';

echo '<td><a href="link2.php">'.$row[1].'</a></td>';

}

}

Try this code.

Any questions feel free to ask.

@strac72authorJun 29.2010 — #Thanks guys. Octoberwind - Good shout about the code there. I had left the tags in my header and footer. That should have been glaring but I have been working on this thing tired a lot of the time. I've sorted that out now but have yet to upload it to the site. What I am aiming to achieve is: when the results are returned on the view_search.php page, their text behaves like a normal hyperlink that would take you to, in the given example of Denmark, ../../pages/d6.php.

iahne - the code looks promising, so, in the example of Header1, Header2, I would just continue with Header3 etc and an additional href echo to each page?

That makes sense but, which element would be replacing Header1? Any field from the database, House_ID? They are just numbers from 1-24.

I should know this probably but i had to miss about 5 weeks from my course when we were mainly doing php.

Thanks again, you both.

@iahneJun 29.2010 — #the code looks promising, so, in the example of Header1, Header2, I would just continue with Header3 etc and an additional href echo to each page?[/QUOTE] -exactly.

And about replacing Header 1, it's up to you. Headers serves as titles of <td> tag. They are showed once only unless you include them on a loop. You can assign values on header depending on what data it is representing on a <td>.

Ex.

<th>Item description</th>

<th>Quantity</th>

<tr>

<td>Candy</td>

<td>12</td>

</tr>

<tr>

<td>Chocolate</td>

<td>5</td>

</tr>

The first <th> represents the candy. The second <th> represents the 12. So as the chocolate and it's quantity of 5 as well being represented by the two <th> tags. Specifically speaking, <th> values usually taken not from a database anymore but from your idea of what data that <th> tag will be representing.

@strac72authorJun 29.2010 — #Thanks Iahne. I will work with it tomorrow as I have to get ready for the penultimate college session - the pressure's on! I'll keep you posted. Paul

@strac72authorJun 30.2010 — #Morning all.

I'm a bit confused. Your code works after a fashion Iahne, I'm sure I'm implementing it incorrectly. I really am uneducated in php. Here is my code and yours, iahne, together. The result on the page is good but not entirely what I need.

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">

<head>

<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<title>Manning & Davidson - Estates of Distinction</title>

<link href="../css/manning&davidson.css" rel="stylesheet" type="text/css" />

<?php # Script - index.php

$page_title = 'Manning & Davidson - Search results';

include ('../includes/header2.php');

?>

<h1 id="mainhead">Search Results</h1>

 

<h5>All prices are quoted in Euros</h5>

<?php

//Use stripslashes() to combat the magic quotes if necessary.

$name = stripslashes($_POST['country']);

?>

<?php

require_once ('../includes/mysql_connect.php'); // Connect to the db.

// Determine where in the database to start returning results.

// Use the default sorting order.

$order_by = 'house_id DESC';

// Make the query.

$query = "SELECT house_id, country, house_type, bedrooms, price, heating, garage FROM houses WHERE country = '$name' ORDER BY $order_by";

$result = mysql_query ($query); // Run the query.

if(mysql_num_rows($result)>0)

{

echo '<table cellpadding="1" cellspacing="1" border="0">';

echo '<th>1</th>';

echo '<th>2</th>';

while($row=mysql_fetch_row($result))

{

echo '<td><a href="t1.php">'.$row[0].'</a></td>';

echo '<td><a href="t2.php">'.$row[1].'</a></td>';

}

}

?>

<?php

// Table header.

echo '<table align="center" cellspacing="5" cellpadding="2">

<tr>

<td align="left"><b>House ID</a></b></td>
  <td align="left"><b>Country</a></b></td>
  <td align="left"><b>House Type</a></b></td>
 
  <td align="left"><b>Bedrooms</a></b></td>
  <td align="left"><b>Price</a></b></td>
  <td align="left"><b>Heating</a></b></td>
  <td align="left"><b>Garage</a></b></td>

</tr>

';

// Only do this if there is a result

// Fetch and print all the records.

while ($row = mysql_fetch_array($result, MYSQL_ASSOC))

{

 echo '<tr>
   <td align="left">' . $row['house_id'] . '</td>
   <td align="left">' . $row['country'] . '</td>
   <td align="left">' . $row['house_type'] . '</td>
   <td align="left">' . $row['bedrooms'] . '</td>
   <td align="left">' . $row['price'] . '</td>
   <td align="left">' . $row['heating'] . '</td>
   <td align="left">' . $row['garage'] . '</td>
 
  </tr>
  ';

}

echo '</table>';

?>

<?php

include ('../includes/footer1.php');

?>

The previous results are not returned at all now, I'm sure there's conflict in the queries but I'm not sure how to sort it out.

The page URL again: http://stracwebdesign.co.uk/mannerings/pages/search.php

and the view_search.php coming up after the query.

Baffled! Could an AND work here? Steep learning curve here.

@iahneJun 30.2010 — #$query = "SELECT house_id, country, house_type, bedrooms, price, heating, garage FROM houses WHERE country = '$name' ORDER BY $order_by";

$result = mysql_query ($query); // Run the query.

if(mysql_num_rows($result)>0)

{

echo '<table cellpadding="1" cellspacing="1" border="0">';

echo '<th>1</th>';

echo '<th>2</th>';

while($row=mysql_fetch_row($result))

{

echo '<td><a href="t1.php">'.$row[0].'</a></td>';

echo '<td><a href="t2.php">'.$row[1].'</a></td>';

}

}

?>[/QUOTE]

on that code of yours you forgot the normal settings of a table. Usually a table is composed of :

<table>

<th></th>

<tr>

<td></td>

</tr>

</table>

Check the table where you output the results. <tr> tag and </table> tag was omitted.

@strac72authorJun 30.2010 — #Hi iahne,

I have worked out a paltry solution for now. I'm a bit frazzled actually and seem to have temporaliy lost the ability of lateral thinking. I'll come back to this later but for now, I have just ran out of time! Please take a look at:

http://stracwebdesign.co.uk/mannerings/pages/search.php

and see how close I was.

Many thanks for helping me with your suggestions - I will persevere! ?

City & Guilds Level 3 is a start at least.

@iahneJun 30.2010 — #if that was the output you've been looking for, all you need to add is a border to make it look like a table. And a link. You can do it.

@strac72authorJul 01.2010 — #That's agood idea on the border. There is a link but for some reason it doesn't work as a link in firefox and chrome, it does in ie, opera and maxthon.

I know different browser work differently but a non working link is a bit odd.

I can do it. I just need a decent grounding in php to make life easier!

Also in #PHP _↴

parse error in php Can I update a Cookie value wherever I am on my site?user pages

Success!

Help @strac72 spread the word by sharing this article on Twitter...

Tweet This

Clickable MySQL database results

11 Comments(s) _↴

Also in #PHP _↴

Success!

Social

Version

Clickable MySQL database results

11 Comments(s) ↴

Also in #PHP ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

11 Comments(s) _↴

Also in #PHP _↴