[SOLVED] Help with mySQL query

[eliZZZa]

Hi Freaks!

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

 

=====START CODE=====

<?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> %s %s =><br /><a class=\"forward\" href=\"http://www.crimeZZZ.net/serialkillers/%s/%s.php\">%s</a></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 %s</a> <img src=\"images/flags/%s.png\" alt=\"%s\" /> \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 "";	
}
?>

=====END OF CODE=====

 

added code tags ~ CV

[.josh]

You could make a loop....? I think that's what you're wanting to do...right? I'm seeing several queries in this code, so are you trying to somehow condense all that code into one query, or are you wanting to repeat that entire block of code over and over again? Need more info please.

[phpzone]

i'm not sure if i'm on the right tracks, but i looked at your site and noticed that you have created a separate PHP file for each

'letter', he's a couple of code snips if this is your issue, otherwise please advise:

<?php

// this creates A-Z links for your navigation

$letters = range('a','z');
foreach( $letters as $letter )
{

	print <<<_LINK
	<a href="xserienkiller?letter=$letter">{$letter}</a>
_LINK;

}
?>

 

<?php
$letter =@ substr($_REQUEST['letter'], 0, 1); // force only 1 character allowed

// this would create the sql to look it up
if ( $letter <> '' )
{

	// How many entries
	$result = mysql_query("SELECT * FROM person WHERE 0100_lastname LIKE \"{$letter}%\" ORDER BY 0100_lastname ASC",$db);	

	// ... rest of code here ...

}

?>

[eliZZZa]

Thanks for your answers, yes, what I want, is to create a loop, which generates each div including the listings.

I think I will be able to manage it according to your advice. I try it and let you know!

 

Meantime - Thanxalot!

eliZZZa

[eliZZZa]

Okay, I managed to generate the A-Z index with PHP.

Now I am trying to do the listing task >;o)

[eliZZZa]

I think I need some "for letters a through z... and then my code...", right?

Could you please help me with the "for letters a through z"-part?

 

Thank you in advance

eliZZZa

[eliZZZa]

Oh my, I did it!!!

Thank you so much for your help!

Hugs & kisses >;o)

 

eliZZZa