PHP MYSQL SEARCH ENGINE

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
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

PHP MYSQL SEARCH ENGINE

Post by romeo »

I'm getting an error with eth fecthj_array statement... anyhelp... this is a search database script that recieves data from a form

$host = "localhost";
$user = "****";
$password = "****";
$dbname = "****";
$tablename = "****";



if ($set != "") { $statement .= "number like %$set%,"; }
if ($number != "") { $statement .= "number like %$number%,"; }
if ($attack != "") { $statement .= "attack $attackcomp $attack,"; }
if ($defense != "") { $statement .= "defense $defensecomp $defense,"; }
if ($icon != "") { $statement .= "icon = \"$icon\","; }
if ($attribute != "") { $statement .= "attribute = $attribute,"; }
if ($level != "") { $statement .= "level = $level,"; }
if ($rarity != "") { $statement .= "rarity like %$rarity%,"; }

$link = mysql_connect($host, $user, $password);

$query = "select * from $tablename where $statement order by id";

echo $query;

$result = mysql_db_query($dbname, $query, $link);


// formatting below
$count = 1;
$column = 1;

print("<table width=\"400\">");


//loop statement

while($row = mysql_fetch_array($result))
{
print("");
josa
Forum Commoner
Posts: 75
Joined: Mon Jun 24, 2002 4:58 am
Location: Sweden

Post by josa »

If you do an echo $query; you will find that your query has a few syntactical errors. You have to rewrite the statements that assemble the where definition.
Hints: You can't use comma in a where definition, use AND instead. Strings must have quotes. (i.e. number like '%foo%')
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

shiat

Post by romeo »

whee, thanks
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

Another question

Post by romeo »

OKay this is what i have now.. and it works.. i just don't know HOW

$link = mysql_connect($host, $user, $password);
$query = "select cotd.number, cotd.id, cards.number, cards.name, cards.description, cards.type, cards.rarity from cotd, cards where cotd.number = cards.number order by cotd.id ASC limit 0, 1";
$result = mysql_db_query($dbname, $query, $link);

while($row = mysql_fetch_array($result))
{

if(!$row[rarity]) { $row[rarity] = "Common"; }
// this is the column 1

print('

<table width="160" border="0" cellspacing="0" cellpadding="0" align="center">
<tr valign="top" align="left">
<td><font face="Verdana, Arial, Helvetica, sans-serif" size="1">Name:</font></td>
<td><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
'."$row[name]".'
</font></td>
</tr>
<tr valign="top" align="left">
<td><font face="Verdana, Arial, Helvetica, sans-serif" size="1">Number:</font></td>
<td><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
'."$row[number]".'
</font></td>
</tr>
<tr valign="top" align="left">
<td><font face="Verdana, Arial, Helvetica, sans-serif" size="1">Type:</font></td>
<td><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
'."$row[type]".'
</font></td>
</tr>

<tr valign="top" align="left">
<td><font face="Verdana, Arial, Helvetica, sans-serif" size="1">Rarity:</font></td>
<td><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
'."$row[rarity]".'
</font></td>
</tr>

<tr valign="top" align="left">
<td height="20"><font face="Verdana, Arial, Helvetica, sans-serif" size="1">Description:
</font></td>
<td height="20"><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
'."$row[description]".'
</font></td>
</tr>
<tr valign="top" align="left">
<td colspan="2"><font face="Verdana, Arial, Helvetica, sans-serif" size="1"><a href="cotd.phtml">Worth
your time?</a></font></td>
</tr>
</table>
');


}


HOW THE HECK DOES THE SCRIPT KNOW WHAT DATABASE FIELD IT"S GOING TO ECHO IF 2 TABLES WITH THE SAME FIELD NAMES ARE QUERIED?
User avatar
Stoker
Forum Regular
Posts: 782
Joined: Thu Jan 23, 2003 9:45 pm
Location: SWNY
Contact:

Post by Stoker »

If you are quering for columns withe the same name in two tables you get an ambigous error unless you alias the column name...

SELECT familyname, husband.name AS hubby, wife.name AS wife
FROM family
LEFT JOIN husband ON husband.family_id = family.family_id
LEFT JOIN wife ON wife.family_id = family.family_id
WHERE familyname = 'Gates'
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

Post by romeo »

Ahh you get an error unless you use AS to identify it differently?
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

still a little lost

Post by romeo »

I have this also

$link = mysql_connect($host, $user, $password);
$query = "select cotd.number, cotd.id, cards.number, cards.name from cotd, cards where cotd.number = cards.number order by cotd.id DESC";
$result = mysql_db_query($dbname, $query, $link);


print('<table width="160" border="0" cellspacing="0" cellpadding="0" align="center">');

while($row = mysql_fetch_array($result))
{


print('


<tr valign="top" align="left">

<td><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
'."$row[name]".'
</font></td>
</tr>
<tr valign="top" align="left">

<td><font face="Verdana, Arial, Helvetica, sans-serif" size="1">
'."$row[number]".'
</font></td>
</tr>

');


}

print('</table>');



problem is I only want to see each instance of a cards.name with it's corresponding cards.number once in the table i'm producing (right now thier are multiple instances of cotd.number) ...

Anyclue?

All help is appreciated!!!
romeo
Forum Contributor
Posts: 138
Joined: Sun Apr 21, 2002 12:50 pm

Post by romeo »

would it be GROUP BY COTD.NUMBER

and can someone please explain to me how that works

I'm back to a previous question...

I just grouped a bunch of cotd.numbers.. what determines what cotd.data i pull down... I see i can pull it down but its only 1 record.. what made that determination?
Post Reply