Help with mySQL query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
eliZZZa
Forum Newbie
Posts: 1
Joined: Fri May 23, 2008 10:19 am

Help with mySQL query

Post by eliZZZa »

I'm a beginner on PHP and mySQL and I need your help with writing a query more efficiently.

I am querying data from a mySQL database resulting in alphabetical lists of persons. What I do (and I am sure, there is a more elegant way for it) is repeating the following query for every list. Is there a way to write the query only once?
Your help is very much appreciated!

The page with the query can be found under
crimezzz dot net slash avis slash index dot php

Code: Select all

 
<?php
$db = mysql_connect("localhost", "dbxxxxx", "xxxxx");
mysql_select_db("xxxxx",$db);
 
# How many entries
$result = mysql_query("SELECT * FROM person WHERE 0100_lastname LIKE \"A%\" ORDER BY 0100_lastname ASC",$db);
$num = mysql_num_rows($result);
echo "<h4>$num Entries</h4>\n";
# Ende Wieviele Einträge
            
# Start query
if ($myrow = mysql_fetch_array($result)) {
 
  do {
  $forwardid = $myrow["forward_noyes"];
 
# IF forward
  if ($forwardid == 2) {
   $string = $myrow["0120_case_code"];
   $folderv = $string{0};
   printf ("<li><span class=\"fwd\">></span>&nbsp;%s&nbsp;%s&nbsp;=>
<a class=\"forward\" href=\"http://www.crimeZZZ.net/serialkillers/%s/%s.php\">%s[/url]</li>\n", $myrow["0100_lastname"], $myrow["0110_firstname"], $folderv, $myrow["0120_case_code"], $myrow["0120_case_code"]);
  }
 
# STANDARD Eintry
  else {
 
//Query Color Gender aus source_gender, in Übereinstimmung mit Ziffer aus 0300_gender
   $genderid = $myrow["0300_gender"];
   $gender = mysql_query("SELECT * FROM source_gender WHERE id = $genderid");
      if ($rowgender = mysql_fetch_array($gender)) {
      do { printf ("<li><span class=\"%s\">\n", $rowgender["gender"]);
      } while ($rowgender = mysql_fetch_array($gender));
      } else { printf ("");
}
 
# Query recent_status
   $statusid = $myrow["0600_recent_status"];
   $status = mysql_query("SELECT * FROM recent_status WHERE id = $statusid");
   $rowstatus = mysql_fetch_array($status);
   $string = $rowstatus["name"];
   $stringfirst = $string{0};
   printf ("%s</span>\n", $stringfirst);
   
   $string = $myrow["0120_case_code"];
   $folderv = $string{0};
    $string = $myrow["0300_gender"];
   $genderv = $string{0};
   
   printf ("<a href=\"http://www.crimeZZZ.net/serialkillers/%s/%s.php\" class=\"gender%s\">%s&nbsp;%s[/url]&nbsp;<img src=\"images/flags/%s.png\" alt=\"%s\" />&nbsp;\n", $folderv, $myrow["0120_case_code"], $genderv, $myrow["0100_lastname"], $myrow["0110_firstname"], $myrow["1601_crcounty1"], $myrow["1601_crcounty1"]);
   printf ("<span class=\"suspected\">%s</span></li>\n", $myrow["1830_victims_suspected"]);
 
}
             
   } while ($myrow = mysql_fetch_array($result));
 
} else {
   echo "";   
}
?>
 
Post Reply