help with simple database code

theBunnyMaster

Hi!

I am new to PHP and this is my first project using that language. So I would appreciate a little help with my code. I've wrote the following code to search for some items in my events database. I think the problem, why this code doesn't work, is in the time period calculating section or the search function... Anyways, here's the code itself:

<html>
<head>
<title>Neeme Küla Ürituste Andmebaas - Tulemused</title>
</head>

<body>

<table>
<tr><td>KUUPÄEV</td><td>KELL</td><td>NIMI</td><td>KIRJELDUS</td><td>KOHT</td><td>KORRALDAJA</td><td>KONTAKT</t

d><td>FAIL</td><td>MUU</td>

<?PHP

$db = mysql_connect("localhost", "user", "password");
mysql_select_db("database", $db);
$s_algus_paev = $POST['p_algus_paev'];
$s_algus_kuu = $POST['p_algus_kuu'];
$s_algus_aasta = $POST['p_algus_aasta'];
$s_lopp_paev = $POST['p_lopp_paev'];
$s_lopp_kuu = $POST['p_lopp_kuu'];
$s_lopp_aasta = $POST['p_lopp_aasta'];
$s_fraas = $POST['fraas'];
$s_koht = $POST['koht'];

$arv_paev = $s_lopp_paev - $s_algus_paev;
$arv_kuu = $s_lopp_kuu - $s_algus_kuu;
$arv_aasta = $s_lopp_aasta - $s_algus_aasta;

$paevi = $arv_paev + ($arv_kuu 31) + ($arv_aasta 372) + 1;
$curPaev = $s_algus_paev - 1;
$curKuu = $s_algus_kuu;
$curAasta = $s_algus_aasta;
$totPaev = $s_algus_paev + ($s_algus_kuu 31) + ($s_algus_aasta 372);

for($a = 0; $a < paevi; $a++)
{
$curPaev = $curPaev + 1;
if($curPaev > 31) {
$curKuu = $curKuu + 1;
$curPaev = 1;
}
if($totPaev > 372) {
$curKuu = 1;
$curPaev = 1;
$curAasta = $curAasta + 1;
}
$final_date = "$curPaev.$curKuu.$curAasta";
$key = $final_date . ' ' . $s_fraas . ' ' . $s_koht;
$search = "SELECT *,
MATCH(kuup2ev, kell, nimi, kirjeldus, koht, korraldaja, kontakt, fail, muu) AGAINST('$key') AS score
FROM syndmused
WHERE MATCH(kuup2ev, kell, nimi, kirjeldus, koht, korraldaja, kontakt, fail, muu) AGAINST('$key')
ORDER BY score";

$ret = mysql_query($search);
while($row = mysql_fetch_array($ret)) {
echo "<tr><td>{$row[kuup2ev]}</td>";
echo "<td>{$row[kell]}</td>";
echo "<td>{$row[nimi]}</td>";
echo "<td>{$row[kirjeldus]}</td>";
echo "<td>{$row[koht]}</td>";
echo "<td>{$row[korraldaja]}</td>";
echo "<td>{$row[kontakt]}</td>";
$adre = '"' . $row[fail_aadress] . '"';
$failinimi = $row[fail];
echo "<td><a href=" . $adre . ">" . $failinimi . "</a></td>";
echo "<td>{$row[muu]}</td></tr>";
}
echo "</table>";
}

?>

</body>

</html>

If you need to see the form, look here:
http://www.tbmsoftware.pri.ee/nkya.htm
(the page itself is in Estonian)

Thank you for your help!

linus

I think it would help if you (1) added some comments to the code, and (2) told us exactly what's not working -- are you getting an error or is something unexpected happening?

theBunnyMaster

//Search!!! (Full-text)
$search = "SELECT *,
	MATCH(kuup2ev, kell, nimi, kirjeldus, koht, korraldaja, kontakt, fail, muu) AGAINST('$key') AS score
	FROM syndmused
	WHERE MATCH(kuup2ev, kell, nimi, kirjeldus, koht, korraldaja, kontakt, fail, muu) AGAINST('$key')
	ORDER BY score";

$result = mysql_query($search);

So I did some more debugging and finally found, that the error must be in the part of the script above. It seems, that there is no valid resource in the variable $search, because when I try to fetch an array with MYSQL_ASSOC, it says "mysql_fetch_array(): supplied argument is not a valid MySQL result resource" and echo "And the result is: $result" prints only the line "And the result is: "
Can anyone please help me to find out why it doesn't perform a search? Is there anything wrong with my syntax?
Any help is appreciated...

kburger

First, it would help if you'd wrap your code in [ PHP ] [ /PHP ] tags. It makes the code much more readable.

I'd sort of like to see your table structure. Assuming it's set up correctly, if you're searching for something that's in more than half the rows using a fulltext search, MySQL basically throws that out as being not much of a match. Also, by default, keywords of less than 4 characters are also thrown out. Could that possibly be why you're not getting any results? There's some documentation here.

theBunnyMaster

My table has been created using this code:

$result = "CREATE TABLE yritused (kuup2ev VARCHAR(10), kell VARCHAR(20), nimi VARCHAR(100), kirjeldus VARCHAR(200), koht VARCHAR(70), korraldaja VARCHAR(50), kontakt VARCHAR(70), fail VARCHAR(15), fail_aadress VARCHAR(300), muu VARCHAR(50))";

The table itself works, I've added stuff there for debugging with my own adding application. I have also checked that the data is entered correctly using phpMyAdmin
I have 3 entries, with only one entry containing the phrase "16.06.2005", but with the keyword "16.06.2005 " it still won't find anything...

kburger

I don't see any fulltext keys for this table.

theBunnyMaster

Oh...
Can I ask, what are fulltext keys for and how can I add them to my table?

kburger

Since you mentioned phpMyAdmin, that's probably the easiest way to do it. Go to your table structure and look for the "Indexes" box. You'll want to "Create an index on '9' columns", click "GO", select index type: "FULLTEXT", and select your 9 fields. Click "Save".

Now that I look at it a little bit I'm wondering why you're trying to index 9 fields. Index only those you want to search on. Take a look at the link I posted previously. That should explain about fulltext searches in MySQL.