Query Not Working

hansman

Hello, With my query here, i run it in phpmyadmin, but for some reason i cannot get it to run while i have it in php

 if (preg_match("/^\w{1,10}$/", $zip, $matches))
 {
 echo $zip;
$query = "SELECT Companies.Name, Companies.Phone, Companies.ZipCodes, Zips.ZipCode FROM Companies, Zips Where Zips.ZipCode='$zip'"; 
}

 else{ // we don't bother querying the database
   echo "Not A Valid Zip Code";
}

while($row = mysql_fetch_array($query)){
echo "Zip Code Phone Numbers: " . $row['Companies.Phone'];
}

Any ideas?

Ashley_Sheridan

Have you made sure you have connected to the database from within PHP?

If not, this code will do the trick:

$dbhost = 'localhost';
$dbname = 'database_name';
$dbuser = 'root';
$dbpasswd = '';

// access MySQL
$dbc = mysql_connect ($dbhost, $dbuser, $dbpasswd);

// access the database
mysql_select_db ($dbname);

hansman

yup, this code is placed shortly before it

mysql_connect("xxxxxxx", "xxxxxx", "xxxxxxx");
mysql_select_db("xxxxxxxx") or die( "Unable to select database");

Ashley_Sheridan

Are you getting any errors from the two connection lines? I presume that it is echoing out the $zip variable correctly?

hansman

i figure out what was wrong.. now i get this error

Here is my code

SELECT Companies.Name, Companies.Address, Companies.Website, Companies.Email, Companies.Services, Companies.Fax, Companies.Phone, Companies.ZipCodes, Zips.ZipCode, Zips.StateAbbr FROM Companies, Zips WHERE Companies.ZipCodes LIKE \&#37;'$zip'%\

i get the error

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\%'10511'%\' at line 1

Ashley_Sheridan

You are escaping the wrong parts of the string. After LIKE, the SQL statement should read: '%$zip%'

hansman

this returns a TON of results, with the company information being the same.

I have the zip code in the Companies.ZipCodes. The field looks like 10024, 10511, etc.

I want the user to search ($zip) and have it select any company with $zip in the ZipCodes field

Ashley_Sheridan

You have two tables, Zips and Companies. What is the common field for the two, as you seem to be trying to extract information based on both tables, but without joining them in any way, so your results will be very erratic.

hansman

i have a table called Zips, which has zip codes, all unique.

I have another table, Companies. In the table companies, i have a field for zip codes, all seperated by commas. I have some companies with similar zip codes.

When a user searches their zip code the companies containing that zip code will come up.

I was looking into the IN statement with SQL.

What would you recommend i do for this

Ashley_Sheridan

You say you're getting duplicate results in PHP, are you sure you're not getting duplicates in phpMyAdmin?

hansman

exactly

Ashley_Sheridan

There's no reason that this should be happening. Are you definately sure you are using the exact same query in both phpMyAdmin and your PHP script? phpMyAdmin obviously is written in PHP, so the query should return exactly the same results as a PHP script.

hansman

when i run a search on my table in phpmyadmin, and i click "Generate PHP" thi sis what I get

SELECT *  FROM `Companies` WHERE `ZipCodes` LIKE CONVERT(_utf8 '%10567%' USING latin1) COLLATE latin1_german2_ci

This returns one company listing

Ashley_Sheridan

Put that exact query into your PHP script then.

hansman

AWESOME thank you very much

Ashley_Sheridan

SELECT * FROM Companies WHERE ZipCodes LIKE CONVERT(_utf8 '%10567%' USING latin1) COLLATE latin1_german2_ci

is not the same as

SELECT Companies.Name, Companies.Address, Companies.Website, Companies.Email, Companies.Services, Companies.Fax, Companies.Phone, Companies.ZipCodes, Zips.ZipCode, Zips.StateAbbr FROM Companies, Zips WHERE Companies.ZipCodes LIKE CONVERT(_utf8 '%10567%' USING latin1) COLLATE latin1_german2_ci

You are adding an extra table into your query, which is throwing the results right out.

hansman

i have table of zip codes, each zipcode has a town state etc..
and i have the table of Companies, in that table there is a field where i put all the zip codes fro that company, separated by commas

When a user searches there own zip code, company info will come up, and information about the zip code will come up.

Someone mentioned making a third table to combine them, or using JOIN

how would this query be written?

Ashley_Sheridan

You would have a third table laid out a bit like this:

id  |  zip  |  companyID
----+-------+-----------
1   |xxxxx  |  673
2   |xxxxx  |  673
3   |xxxxx  |  512
4   |xxxxx  |  429

The first column is a unique identifying field, the second contains the zip code and the third is the company id (which is the company id used in the Companies table). For each zip code which you want to associate with a company, you would use a new row in this new table, specifying the zip code and the company id. Only the first column of this table is unique, so you can duplicate the company id as many times as required.

Your query can then perform a search on this new table without wildcards (which is quicker and less memory intensive) and join in the rest of the information from the other two tables as it needs:

SELECT * FROM Companies, Zips, CompanyZips WHERE CompanyZips.companyID = Companies.id AND CompanyZips.zip = Zips.ZipCode AND CompanyZips.zip = '$zip'

There is more information on joining 3 tables in MySQL at http://www.wellho.net/solutions/mysql-left-joins-to-link-three-or-more-tables.html