Skip site navigation (1) Skip section navigation (2)

SQL query seach + rearranging results

From: "lowdog" <lowdog(at)axelero(dot)hu>
To: <pgsql-sql(at)postgresql(dot)org>
Subject: SQL query seach + rearranging results
Date: 2004-02-16 16:07:30
Message-ID: 004001c3f4a6$fb7ede50$1ec3fea9@rodrigez (view raw, whole thread or download thread mbox)
Lists: pgsql-sql
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  

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(at)rubinfleur(dot)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...: 

if ($totalRows_nevnapok_lekerdezes <= 0) { 
$hibauzenet = "Ilyen név nincs az adatbázisban: "; 
print $hibauzenet; 
print $p_name;} 
{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: "o" "u". 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? 


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';

  nev_kod smallint(6) NOT NULL default '0',
  nev text NOT NULL,
  PRIMARY KEY  (nev_kod)
) TYPE=MyISAM COMMENT='nevek listaja';

CREATE TABLE nevnapok (
  entry_kod smallint(6) NOT NULL default '0',
  nev_kod smallint(6) NOT NULL default '0',
  honap_kod tinyint(2) NOT NULL default '0',
  nap tinyint(2) NOT NULL default '0',
  PRIMARY KEY  (entry_kod)
) TYPE=MyISAM COMMENT='a nevnapok...';


pgsql-sql by date

Next:From: Tom LaneDate: 2004-02-16 16:30:34
Subject: Re: Date format problems
Previous:From: Mark GibsonDate: 2004-02-16 16:05:17
Subject: Tip: a function for creating a remote view using dblink

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group