Thread: SQL query seach + rearranging results

hey guys!

I need your help in writing a php name-day searcher script.

here's what ive done:

i have the hostname, login and pw defined in a file, this one calls it:
<?php require_once('rubin_nevnapok.php'); ?>

then i made the query, where (sorry for not translating the variable names, would be easier for you to understand )
the "nevek" table contains the names, the honapok table has the month names and the "nevnapok.nap" tells which day of the month has the name (nevek.nev).
hope you understand me, i'm a bit confused


<?php
mysql_select_db($database_rubin_nevnapok, $rubin_nevnapok);
$honap_szama = Date("m");
$nap_szama = Date("d");
$query_mainevnap_lekerdezes = "SELECT nevek.nev, honapok.honap_kod, nevnapok.nap FROM nevek INNER JOIN (honapok INNER JOIN nevnapok ON honapok.honap_kod = nevnapok.honap_kod) ON nevek.nev_kod = nevnapok.nev_kod WHERE honapok.honap_kod = '$honap_szama' AND nevnapok.nap = '$nap_szama'";
$mainevnap_lekerdezes = mysql_query($query_mainevnap_lekerdezes, $rubin_nevnapok) or die("MySQL hiba! webmaster@rubinfleur.hu " . mysql_error());
$row_mainevnap_lekerdezes = mysql_fetch_assoc($mainevnap_lekerdezes);
$totalRows_mainevnap_lekerdezes = mysql_num_rows($mainevnap_lekerdezes);
?>

so here comes the important code, which i need help in...:


<?php
if ($totalRows_nevnapok_lekerdezes <= 0) {
$hibauzenet = "Ilyen név nincs az adatbázisban: ";
print $hibauzenet;
print $p_name;}
else
{echo $row_nevnapok_lekerdezes['nev'];
do {
echo " napja:";
echo "<br>";
echo $row_nevnapok_lekerdezes['honap'];
echo " ";
echo $row_nevnapok_lekerdezes['nap'];
echo ".<br>";
} while ($row_nevnapok_lekerdezes = mysql_fetch_assoc($nevnapok_lekerdezes)); } ?>
here if the totalrows is 0 then the name ($p_name entered in a html form btw) was not found in the database and prints an error message (which is btw $hibauzenet). from now comes the problem:
one day (one date) CAN have more names, and
one name CAN be on more days...

now what happens is that if theres a name that has more days, the name is written once and the corresponding dates ('honap' is the month, 'nap' is the day) are printed (if there are more dates of the specific name, if there is only one, that one is printed).
BUT the mysql query as you can see, querys using WHERE nevek.nev LIKE "%$p_name%"... this has a reason: there should be matches if the user enters only a part of the name, and
therefore if they enter only one letter (say "a") there will be a lot of names, the ones containing that letter... and in this case, only the first match of the name is printed,and all the other dates...
So how can i make the script write the "nev" if there are different names, but i want it to write the name only once if the specific name has two or more dates according to the database?

i know should've expressed my problem simplier but i couldn't for that, i apologise! but i hope you have my point and can help me!

unfortunately i'm not a php expert, i tried some things but they didn't work for me... i tried to do the printing with a for, where the $i keeps changing while it checks if the consecutive result rows (mysql_result($nevnapok_lekerdezes,$i) and $eredmeny2 = mysql_result($nevnapok_lekerdezes,$i+1)) are the same, and if they are, writes the name only once, but i might have screwed it up because it did not do what i meant it to do

there's another problem: my language has characters that the regular code tables do not contain, in html they are shown by: "ő" "ű". some names contain some of these, and they are not found by the above script, although i have added them to the mysql database... for example if there's a name like "Dezsõ" (hope you will see this correctly, the last letter is the 337), it's not found if i enter
exactly this into the search field, but is found if i do a query with "Dezso"
written in the field (now it's the same letter, but has no commas on its top), it is found, and the printed name is not Dezso but Dezsõ, so the database keeps the special letter, just can't find it!
what do you suggest?


regards,
lowdog
lowdog@axelero.hu

ps: i attach here the create tables, just to make the above more understandable:

CREATE TABLE honapok (
  honap_kod tinyint(2) NOT NULL default '0',
  honap text NOT NULL,
  PRIMARY KEY  (honap_kod)
) TYPE=MyISAM COMMENT='honapok';