selecting certain info from fields in mySQL

php script? for mySQL

[fiddler]

Hi - Soooo new to php and mySQL here that am lost on some of the terminology, so don't know if my questions have even been addressed/answered in other posts - if so, please forgive my ignorance...

I have a membership database, which has the names, addresses, etc. of members in 9 states.

I have a php script that brings up all the info of each member in order by Name.

I want to be able to select a state, and have all the members of just that state come up on my list.

I also want
1. the Name to come up in bold
and 2. the e-mail and web site info to come up as hyperlinks.

Below is my script as it currently functions.
Can anyone tell me what do I need to change/add to get the info to be presented as I would like?

<?

$db_name ="members";

$table_name ="Members_Directory";

$connection = @mysql_connect("localhost","#*$!x","#*$!xx") or die(mysql_error());

$db = @mysql_select_db($db_name,$connection) or die(mysql_error());

$sql = 'SELECT `Name`,`Address1`,`Address2`,`City`,`State`,`Zip`,`Phone`,`Fax`,`Owner`,`County`,`Brands`,`email`,`WebSite` FROM `Members_Directory` LIMIT 0, 500';

$result = @mysql_query($sql,$connection) or die(mysql_error());

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

$Name = $row['Name'];
$Address1 = stripslashes($row['Address1']);
$Address2 = stripslashes($row['Address2']);
$City = stripslashes($row['City']);
$State = stripslashes($row['State']);
$Zip = stripslashes($row['Zip']);
$Phone = $row['Phone'];
$Fax = $row['Fax'];
$Owner = $row['Owner'];
$County = $row['County'];
$Brands = $row['Brands'];
$email = $row['email'];
$WebSite = $row['WebSite'];

$display_block .= "<P><strong>$id</strong>$Name<br>
$Address1 <br>
$Address2 <br>
$City<br>
$State<br>
$Zip<br>
$Phone<br>
$Fax<br>
$Owner<br>
$County<br>
$Brands<br>
$email<br>
$WebSite</P>";
}

?>

[jatar_k]

Welcome to WebmasterWorld fiddler,

you would need to adjust/change the actual query that you are sending to mysql

$sql = 'SELECT `Name`,`Address1`,`Address2`,`City`,`State`,`Zip`,`Phone`,`Fax`,`Owner`,`County`,`Brands`,`email`,`WebSite` FROM `Members_Directory` LIMIT 0, 500';

that is what you are using now. This is a generic query that will return up to 500 results. To make the query more selective you need to add a WHERE clause

$sql = "SELECT `Name`,`Address1`,`Address2`,`City`,`State`,`Zip`,`Phone`,`Fax`,`Owner`,`County`,`Brands`,`email`,`WebSite` FROM `Members_Directory` LIMIT 0, 500 where State='Arizona";

not the exact thing but you would need to get the statename you are looking for into that where clause between the single quotes.

notice one other change, I changed the surrounding single quotes to double quotes to allow me to use single quotes inside of the query with out having to escape them.

[fiddler]

Thanks, jatar_k

but I did a cut and paste of the code, plus tried every number of variation I could think of and only came up with syntax error. Is there something that I'm missing?

[jatar_k]

I think one of my single quotes is MIA

$sql = "SELECT `Name`,`Address1`,`Address2`,`City`,`State`,`Zip`,`Phone`,`Fax`,`Owner`,`County`,`Brands`,`email`,`WebSite` FROM `Members_Directory` LIMIT 0, 500 where State='Arizona'";

[fiddler]

I thought about that and tried to put it in, before I sent my reply (one of the variations I tried)...this time I did a cut and paste, and I am still getting:

You have an error in your SQL syntax near 'where State='NY'' at line 1

Very perplexed here. Sorry to be such a PITA.

[jatar_k]

well, standard error checking then

echo the constructed query to the screen, check it visually, then cut and paste it into a command line to mysql directly, see what happens

should be the same error i would think but I can not tell you really what it would be

check all the columns to make sure the case is right and that there are no typos

make sure all of your single quotes match up

use a query that will actually return a result

ahhh, wait try this way
$sql = "SELECT `Name`,`Address1`,`Address2`,`City`,`State`,`Zip`,`Phone`,`Fax`,`Owner`,`County`,`Brands`,`email`,`WebSite` FROM `Members_Directory` where State='Arizona' LIMIT 0, 500";

[fiddler]

BINGO!
Your last suggestion worked - I cut and pasted and everything came up as it should. Thankyou, thankyou, thankyou!

[coopster]

All clauses used must be given in exactly the order shown in the syntax description. For example, a HAVING clause must come after any GROUP BY clause and before any ORDER BY clause.

SELECT [dev.mysql.com]

Yes, jatar_k caught that and made the correction. I just wanted to add to the other excellent advice offered and say that the MySQL manual [dev.mysql.com] is a very valuable tool. You may want to bookmark it and keep it in the arsenal.

[fiddler]

Well, I'm back. Same problem (sort of), new twist....
When I have the Select by State code (previous messages), for some reason it will only bring up one member for NY....although there are many members. And if I do the same thing for PA, it will bring up 3 members only - not the complete list - and it starts in the middle of the list, not at the beginning. Anyone have any idea why it is so random in presenting the results?

[coopster]

The WHERE clause determines which rows are being selected as possibilities. Paired with the LIMIT clause you narrow that down even more. To get the results to stop picking from the "middle" of the list, you need to have an ORDER BY clause in between there.

SELECT 
FROM 
WHERE 
ORDER BY State, Name 
LIMIT

[fiddler]

I entered the ORDER BY State, etc....but still get the same results. I don't get any errors, so I guess that's something. And when I ask for a search for a particular state that there is only one entry on, nothing comes up. Any ideas?

[coopster]

I usually dump the sql statement to the browser at this point and analyze it. You can copy and paste it to the command line then too. If it is not data related, then I would take a close look at the two clauses just mentioned.

[fiddler]

I've come to the conclusion that it must be data related, although I can't figure out how it comes up with the results that it comes up with.

I've made four separate query scripts, and each one brings up different results.

For the first file, I used the code:

$sql = "SELECT `Name`,`Address1`,`City`,`State`,`Zip`,`Phone`,`Fax`,`email`,`WebSite` FROM `Associates_Directory` ORDER BY 'State' ";

This result LOOKS like in everything is being grouped correctly by State - until I scroll down to the end, where the last 6 entries (following WI entries - I have no listings for WY) are 1 MD, 1 NY, 3 PA, and 1 VT, in that order.

For the second file, I wanted the search to come up in ABC order by Name, so the code I used is:
$sql = "SELECT `Name`,`Address1`,`City`,`State`,`Zip`,`Phone`,`Fax`,`email`,`WebSite` FROM `Associates_Directory` ORDER BY 'Name' ";

This presents me with correct results.

For the third file, I asked for the order to be by States, then Names, so used the code:
$sql = "SELECT `Name`,`Address1`,`City`,`State`,`Zip`,`Phone`,`Fax`,`email`,`WebSite` FROM `Associates_Directory` ORDER BY 'State', 'Name' ";

This gave me the same results as I got with the first file - with 1 MD, 1NY, 3PA, and 1 VT at the very end of the list.

Finally, I attempted to just pull out the NY members, so used the code:
$sql = "SELECT `Name`,`Address1`,`City`,`State`,`Zip`,`Phone`,`Fax`,`email`,`WebSite` FROM `Associates_Directory` where State='NY' ORDER BY State, Name, Address1, City, Zip, Phone, Fax, email, WebSite LIMIT 0, 500";

This resulted in giving me one NY entry (there are 22 altogether), and that entry happened to be the NY entry at the bottom of the list as in search 1 and 3. As for the Limit numbers, I have a total of 485 entries in this table I am using for the search.

To say I'm at wit's end is a GROSS understatement.

I've looked at the entire table, and all the info lies in the correct columns, with no extra spaces. The Names of the States are also the same (NY or PA, etc. - never New York or ny or pa or Pa, etc.).

What would account for these strange results? Anyone?

[fiddler]

ohmigosh - I solved the problem!
While looking at the entire table I didn't see any spaces amiss, so I started to compare each row on an individual basis, and wouldn't you know it, I found some additional spacing mucking up the search. Instead of PA I had (space)(space)PA, etc. No wonder the states were not coming up as they should have - there were only 3 members, out of 22 for that State, that were entered with no space before the PA.

Appreciate all the input - it helped me stay focused on finding a solution.