Page 1 of 1

MYSQL Query, echo to screen and link record via <a href&g

Posted: Wed Sep 04, 2002 4:31 pm
by npereira
Hi,

I am making a query to my DB and echo the results to screen.
I would like the possibility to have each result linked seperatly using a
<a href> so that once you click on the link, it brings you a page that only displays the result you clicked on.

Here is the query and echo to screen of the results from the database;

Code: Select all

tTbl("Les Annonces Classées", "left");
$q1 = mysql_query("SELECT idx, titre FROM annonce ORDER BY date DESC LIMIT 5");
$n1 = mysql_num_rows($q1);
if($n1 == 0)&#123; 
  echo "<p align="center">Aucune annonce classée inscrite.</p><br>\n"; 
&#125; else if($n1 > 0)&#123; 
  echo "<p align="center">Voici les dernières annonces classées. Cliquez sur le titre pour y accéder.</p><br>\n"; 
  while ($r1 = mysql_fetch_array($q1)) &#123; 
    echo "<p class="mm">&#8226; <a href="/annonce_view.php" class="mma">"; 
    echo "<span class="mma">".htmlentities($r1&#1111;"titre"]); 
    echo "</span></a></p><br>\n"; 
  &#125; 
&#125;
This outputs all records to screen. When you click on one of the links, this is the code of the "annonce_view.php" script.

Code: Select all

<?
// Index
creerIndex("SELECT COUNT(*) AS num FROM annonce", $max_annonce);
// Titre
tTbl("Les Annonces Classées - ".navIndex(), "left");
// Annonces
$q1 = mysql_query("SELECT *, DATE_FORMAT(date, '$site_date_jh') AS dateh FROM annonce ORDER BY date DESC LIMIT $commence, $max");
$n1 = mysql_num_rows($q1);
if ($n1 == 0) &#123;
	echo "<p align="center">Il n'y a pas d'annonce classée.</p><br>\n";&#125;
while ($r1 = mysql_fetch_array($q1)) &#123;
	echo "<p><span class="titre">".htmlentities($r1&#1111;"titre"])."</span><br>\n";
	echo "Envoyé par <b><a href="mailto:".htmlentities($r1&#1111;"email"])."" title="Ecrire à l'envoyeur">".htmlentities($r1&#1111;"auteur"])."</a></b> ".htmlentities($r1&#1111;"dateh"])."</p>\n";
	echo "<p>".conv($r1&#1111;"texte"])."</p><br>\n";
&#125;
navBarre("");

// Titre
//tTbl("Apposer une annonce!", "left");
//echo "<p>Vous avez des items a vendre, pourquoi pas apposer une annonce!</p>\n\n";
//

	echo "<b><p align="center">Pour inscrire une annonce, <a href="/annonce.php">Cliquer ICI</a></p></b><br>\n";
?>
This echos every single records to screen. I don't want that. I want the user to only see the record he clicked on.

Your feedback is greatly appreciated.

Posted: Wed Sep 04, 2002 4:54 pm
by mikeq
Each link will need to specify which record to look at

<a href=\"/annonce_view.php?ItemID={$r1['itemid']}\" class=\"mma\">";
echo "<span class=\"mma\">".htmlentities($r1["titre"]);
echo "</span></a>

Now the variable $ItemID or $_GET['ItemID'] (dependent on version of PHP and the register_globals setting in php.ini, read the sticky topic on this if you don't understand) becomes available on annonce_view.php.

So your query on annonce_view.php must be limited by this value

("SELECT *, DATE_FORMAT(date, '$site_date_jh') AS dateh
FROM annonce
WHERE fieldIDtofind = '$ItemID'
ORDER BY date DESC LIMIT $commence, $max");

Posted: Thu Sep 05, 2002 9:16 am
by npereira
Is ItemID suppose to be a field in the "annonce" table?
Cause I don't have that field.
I do have the "idx" filed!

Posted: Thu Sep 05, 2002 9:22 am
by npereira
Got it to work.

Changed the ItemID to idx since idx is the auto-increment field in the table. Thanks for your help.

Posted: Thu Sep 05, 2002 9:44 am
by npereira
Now, I have a link to annonce_view.php in my menu, and because of the changes, there is no records echo'ed, since the link does not tag a ItemID.

Is there a way to tell annonce_view.php that if the link does _NOT_ have a ItemID, to echo all the records? Or do I need a seperate PHP file to do this?

Posted: Thu Sep 05, 2002 10:20 am
by twigletmac

Code: Select all

if (!isset($_GET&#1111;'ItemID'])) {
    $sql = "SELECT *, DATE_FORMAT(date, '$site_date_jh') AS dateh FROM annonce ORDER BY date DESC LIMIT $commence, $max";
} else {
    $sql = "SELECT *, DATE_FORMAT(date, '$site_date_jh') AS dateh FROM annonce WHERE fieldIDtofind = '$ItemID' ORDER BY date DESC LIMIT $commence, $max";
}
$q1 = mysql_query($sql) or die(mysql_error());
Mac

Posted: Thu Sep 05, 2002 10:40 am
by npereira
Something is not right. Now, no matter what, ID or not, echo's all records.

Here is the code;

Code: Select all

if (!isset($_GET&#1111;'ItemID'])) &#123; 
    $sql = "SELECT *, DATE_FORMAT(date, '$site_date_jh') AS dateh FROM annonce ORDER BY date DESC LIMIT $commence, $max"; 
&#125; else &#123; 
    $sql = "SELECT *, DATE_FORMAT(date, '$site_date_jh') AS dateh FROM annonce WHERE idx = '$ItemID' ORDER BY date DESC LIMIT $commence, $max"; 
&#125; 
$q1 = mysql_query($sql) or die(mysql_error());

$n1 = mysql_num_rows($q1);
if ($n1 == 0) &#123;
	echo "<p align="center">Il n'y a pas d'annonce classée.</p><br>\n";&#125;
while ($r1 = mysql_fetch_array($q1)) &#123;
	echo "<p><span class="titre">".htmlentities($r1&#1111;"titre"])."</span><br>\n";
	echo "Envoyé par <b><a href="mailto:".htmlentities($r1&#1111;"email"])."" title="Ecrire à l'envoyeur">".htmlentities($r1&#1111;"auteur"])."</a></b> ".htmlentities($r1&#1111;"dateh"])."</p>\n";
	echo "<p>".conv($r1&#1111;"texte"])."</p><br>\n";
&#125;
navBarre("");

Posted: Thu Sep 05, 2002 10:47 am
by mikeq
npereira wrote:Got it to work.

Changed the ItemID to idx since idx is the auto-increment field in the table. Thanks for your help.
[sarcasm]Welldone[/sarcasm] :) ItemID was just an example, as unfortunately I don't know all the fields in your tables :wink:

Posted: Thu Sep 05, 2002 10:50 am
by mikeq
Put this

Code: Select all

echo "ItemID is :".$_GET&#1111;'ItemID'];
before your if statement to see what is in the variable

Posted: Thu Sep 05, 2002 10:56 am
by npereira
Still the same, it echo's all the records, and the echo you had me put in says;

ItemID is :

Nothing else.

Also, just to put everything in perspective, the table 'annonce' contains theses fields;

idx
titre
texte
auteur
email
date

Posted: Thu Sep 05, 2002 2:38 pm
by twigletmac
What do the links you are using look like? Are they

Code: Select all

annonce.php?ItemID=xxx
or is there no ItemID value, perhaps there's a different one?

Mac

Posted: Thu Sep 05, 2002 2:52 pm
by npereira
Ok, I found it, and it works great.

Here is what I changed;

Code: Select all

&lt;?php
if (isset($id) &amp;&amp; is_numeric($id)) { 
	$sql = "SELECT *, DATE_FORMAT(date, '$site_date_jh') AS dateh FROM annonce WHERE idx = $id";
} else { 
	$sql = "SELECT *, DATE_FORMAT(date, '$site_date_jh') AS dateh FROM annonce ORDER BY date DESC LIMIT $commence, $max";     

?&gt;
It's the $id and not ItemID.

Thanks!