Page 1 of 1

PHP and mySQL search functions...

Posted: Thu Jun 17, 2004 3:42 pm
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

?>

Posted: Thu Jun 17, 2004 3:46 pm
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?

Posted: Thu Jun 17, 2004 3:57 pm
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?

Posted: Fri Jun 18, 2004 1:22 am
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?

Posted: Fri Jun 18, 2004 3:31 am
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

Posted: Fri Jun 18, 2004 3:58 am
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.

Posted: Fri Jun 18, 2004 7:59 am
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

Posted: Fri Jun 18, 2004 8:19 am
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

Posted: Fri Jun 18, 2004 9:48 am
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());

?>

Posted: Fri Jun 18, 2004 9:54 am
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!

Posted: Fri Jun 18, 2004 10:08 am
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>

Posted: Fri Jun 18, 2004 11:18 am
by feyd
Is that query string the real query string? It looks like it is.. so, you may want to change 'namesearch' to '$namesearch'