PHP and mySQL search functions...

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
mogman42
Forum Newbie
Posts: 18
Joined: Fri May 21, 2004 10:00 am

PHP and mySQL search functions...

Post by mogman42 »

New problem....

Why won't this code work?

Code: Select all

$sql=mysql_query("SELECT * FROM data WHERE (rccfname LIKE '%namesearch%') OR (rcclname LIKE '%namesearch%') OR (clientfname LIKE '%namesearch%') OR (clientlname LIKE '%namesearch%')", $link) or die(mysql_error());




Solution Code to Below

Code: Select all

<?php
$sql=mysql_query("SELECT * FROM data where bldg LIKE '%$areasearch'", $link) or die(mysql_error());
?>
Hello, I'm attempting to create a page for a user to input something into a text box or use a pull down menu to specify seach criteria for a mySQL database.

There are 4 different columns I want to search from. I'm trying to build the code one by one for each search. Or if possible, put it all into a nested If or something.

I've using the following code, but I can't get it to generate any results.

Errors are enabled.

Code: Select all

<?php
//Other Code above here...

// Begin submit form variables
$areasearch = $_POST['areasearch'];
$bldgsearch = $_POST['bldgsearch'];
$namesearch = $_POST['namesearch'];
$rccidsearch = $_POST['rccidsearch'];
// End submit form variables

// Connect to DB
$link = mysql_connect('localhost','blah','blah') or die ('Could not connect to mySQL server!: ' .mysql_error() );

mysql_select_db('quickincidents', $link) or die ('Could not select DB!: ' .mysql_error() );

// Search by Area
$sql=mysql_query("SELECT bldg FROM data where bldg = '$areasearch'", $link) or die(mysql_error());
$bg = '#FFFFFF';  //set background color
while ( $row = mysql_fetch_array($sql)) { 
$bg = ($bg=='#FFFFFF' ? '#D8D8D8' : '#FFFFFF'); //switch background color
echo '<tr bgcolor="',$bg,'">'; 
print "<td>" . $row['rccfname'] . "</td>"; 
print "<td>" . $row['rcclname'] . "</td>"; 
print "<td>" . $row['rccid'] . "</td>"; 
print "<td>" . $row['rccemail'] . "</td>"; 
print "<td>" . $row['clientfname'] . "</td>"; 
print "<td>" . $row['clientlname'] . "</td>"; 
print "<td>" . $row['bldg'] . "</td>";
print "<td>" . $row['dormphone'] . "</td>";
print "<td>" . $row['altphone'] . "</td>";
print "<td>" . $row['clientemail'] . "</td>";
print "<td>" . $row['probleminfo'] . "</td>";
}
//End Search by Area 

?>
<!--Make Page/Table headers-->
<h1><strong>ResNet Quick Incident Database Results </strong></h1>
<table border="1" width="100%" cellspacing="2" cellpadding="2"> 
<tr> 
<th> Consultant Fname </th> 
<th> Consultant Lname </th> 
<th> Consultant ID </th> 
<th> Consultant E-Mail </th> 
<th> Client Fname </th> 
<th> Client Lname </th> 
<th> Client Bldg </th> 
<th> Client Dorm Phone </th> 
<th> Client Alt Phone </th> 
<th> Client E-Mail </th> 
<th> Problem Info </th> 
</tr> 
</table>
<p><a href="search.html">Back to Search Page </a></p>
</body>
</html>
feyd|switched to

Code: Select all

tags for readability.[/color]

Code: Select all

<?php

?>

Code: Select all

<?php

?>
Last edited by mogman42 on Fri Jun 18, 2004 9:49 am, edited 3 times in total.
User avatar
markl999
DevNet Resident
Posts: 1972
Joined: Thu Oct 16, 2003 5:49 pm
Location: Manchester (UK)

Post by markl999 »

Check the query is working ok.
$sql=mysql_query("SELECT bldg FROM data where bldg = '$areasearch'", $link) or die(mysql_error());

Also be sure to close the <tr> inside the loop, and unless you didn't paste that bit, i don't see where you open and close the results table?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

what the?

"SELECT bldg FROM data where bldg = '$areasearch'" ... you want it to return a field for which you already know the value?
mogman42
Forum Newbie
Posts: 18
Joined: Fri May 21, 2004 10:00 am

Post by mogman42 »

feyd - for my query I want it to check the following table and search one specific column for a certain piece of data, then return and print out all the rows that have that data in the column.

user enters a value in the search field - say a building name. I want to search a specific column in the table for all the instances of that building name and have it output/print all the rows that contain that data.

I apologize if I'm not being clear and kludging this. I'm still a PHP newb...

Updated code, doesn't throw any errors, but it doesn't print anything either. I'm guessing my query syntax is totally wrong. :oops:

Anybody have any good links to mysql_query syntax/setup for php starters?
PAW Projects
Forum Commoner
Posts: 30
Joined: Tue Jun 15, 2004 7:43 am
Contact:

Post by PAW Projects »

After you carefully read feyd's post again, this would be a good place to start:
http://dev.mysql.com/doc/mysql/en/index.html


SELECT bldg FROM data where bldg = '$areasearch'"

Is the same as saying in English:

I want to know the value of the field 'bldg'
in the table 'data'
where the value of field 'bldg' is this: $areasearch

Don't you want to know the value of the other fields instead?



No, this is his actual code.
He's wondering why he doesn't see any results. His SQL explains why :wink:
V V V V V
Last edited by PAW Projects on Fri Jun 18, 2004 6:09 am, edited 2 times in total.
fastfingertips
Forum Contributor
Posts: 242
Joined: Sun Dec 28, 2003 1:40 am
Contact:

Post by fastfingertips »

Perhaps just he tried to check is he has values but is not interested at the first view by the other fields, but in this case i prefer more:

[mysql_man]
SELECT count(bldg) as resNo FROM data where bldg = '$areasearch'
[/mysql_man]

In this way i'm getting only one field, and i know if i have results and how many :).
Otherwise if you want to filter the result, indeed you may need another fields.

Anyway may i ask you id that options are described by the checkboxex i think is a little bit improper to assign from beginning values, you should check if you have received something first.
mogman42
Forum Newbie
Posts: 18
Joined: Fri May 21, 2004 10:00 am

Post by mogman42 »

PAW Projects wrote:After you carefully read feyd's post again, this would be a good place to start:
http://dev.mysql.com/doc/mysql/en/index.html


SELECT bldg FROM data where bldg = '$areasearch'"

Is the same as saying in English:

I want to know the value of the field 'bldg'
in the table 'data'
where the value of field 'bldg' is this: $areasearch

Don't you want to know the value of the other fields instead?
I want to select the column "bldg" in the table "data" where the value of field "bldg" = $areasearch

I guess I'm still confused on the syntax to set that up properly.

My first step was to check the mysql FAQ/command reference.........still couldn't figure it out....

I don't want anybody to give me the code, just a push in the right direction for the proper syntax/argument I want.........I know the result I want to get, I just can't figure out how to form the mysql_query statement to give it to me!!!!! :oops:

When I run the seach now with the code above, I get the following as a result. The bldg=4 is from the selection I made on the seach page, so I know that part is working at least.
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 'FROM data where bldg = '4' at line 1
PAW Projects
Forum Commoner
Posts: 30
Joined: Tue Jun 15, 2004 7:43 am
Contact:

Post by PAW Projects »

mogman42 wrote:I want to select the column "bldg" in the table "data" where the value of field "bldg" = $areasearch
I doubt that.

You already know "bldg", so you want to know the rest of the data (but only where bldg=$areasearch).

Try: SELECT * from data WHERE bldg=$areasearch
mogman42
Forum Newbie
Posts: 18
Joined: Fri May 21, 2004 10:00 am

Post by mogman42 »

yup, I figured it out! thanks!

New question tho..........

Why won't this display any data?

Code: Select all

<?php
$sql=mysql_query("SELECT * FROM data WHERE (rccfname LIKE '%namesearch%') OR (rcclname LIKE '%namesearch%') OR (clientfname LIKE '%namesearch%') OR (clientlname LIKE '%namesearch%')", $link) or die(mysql_error());

?>
User avatar
Joe
Forum Regular
Posts: 939
Joined: Sun Feb 29, 2004 1:26 pm
Location: UK - Glasgow

Post by Joe »

Try:

Code: Select all

<?php
$sql="SELECT * FROM data WHERE rccfname LIKE '%namesearch%' OR rcclname LIKE '%namesearch%' OR clientfname LIKE '%namesearch%' OR clientlname LIKE '%namesearch%'";
$result = mysql_query($sql) or die(mysql_error());
?>
Just a small modification but im sure it will work!
mogman42
Forum Newbie
Posts: 18
Joined: Fri May 21, 2004 10:00 am

Post by mogman42 »

Hmm, thanks for the idea.........tried the following code.

It runs, dumps me to the results page, but only displays the headers. No errors, no data..........nada.

here is the page code in its entirety

Code: Select all

<?php
<html>
<head>
<title>Search Submit</title>
</head>

<body>
<?
// IP Restriction Code
$ip = join('.', array_slice(explode('.', $_SERVER['REMOTE_ADDR']), 0, 2)); 
if($ip != '132.177'){ 
   die('<font size="5" color="FF0000">[b]You are not authorized to view this page![/b]</font>'); 
} 
// End IP Restriction Code

// Begin submit form variables
$namesearch = $_POST['namesearch'];
// End submit form variables

// Connect to DB
$link = mysql_connect('localhost','blah','blah') or die ('Could not connect to mySQL server!: ' .mysql_error() );

mysql_select_db('quickincidents', $link) or die ('Could not select DB!: ' .mysql_error() );

?>

<!--Setup Table Headers-->

<h1><strong>ResNet Quick Incident Database Results </strong></h1>
<table border="1" width="100%" cellspacing="2" cellpadding="2"> 
<tr> 
<th> Consultant Fname </th> 
<th> Consultant Lname </th> 
<th> Consultant ID </th> 
<th> Consultant E-Mail </th> 
<th> Client Fname </th> 
<th> Client Lname </th> 
<th> Client Bldg </th> 
<th> Client Dorm Phone </th> 
<th> Client Alt Phone </th> 
<th> Client E-Mail </th> 
<th> Problem Info </th> 
</tr> 
<!-- End table headers -->

<?
// Search by Name
$sql=mysql_query("SELECT * FROM data WHERE rccfname LIKE '%namesearch%' OR rcclname LIKE '%namesearch%' OR clientfname LIKE '%namesearch%' OR clientlname LIKE '%namesearch%'" , $link) or die(mysql_error()); ; 
$bg = '#FFFFFF';  //set background color
while ( $row = mysql_fetch_assoc($sql)) { 
$bg = ($bg=='#FFFFFF' ? '#D8D8D8' : '#FFFFFF'); //switch background color
echo '<tr bgcolor="',$bg,'">'; 
print "<td>" . $row['rccfname'] . "</td>"; 
print "<td>" . $row['rcclname'] . "</td>"; 
print "<td>" . $row['rccid'] . "</td>"; 
print "<td>" . $row['rccemail'] . "</td>"; 
print "<td>" . $row['clientfname'] . "</td>"; 
print "<td>" . $row['clientlname'] . "</td>"; 
print "<td>" . $row['bldg'] . "</td>";
print "<td>" . $row['dormphone'] . "</td>";
print "<td>" . $row['altphone'] . "</td>";
print "<td>" . $row['clientemail'] . "</td>";
print "<td>" . $row['probleminfo'] . "</td>";
print "<tr>";
}
// End Search by Name
?>
</table>
<p><a href="search.html">Back to Search Page </a></p>
</body>
</html>
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

Is that query string the real query string? It looks like it is.. so, you may want to change 'namesearch' to '$namesearch'
Post Reply