PHP/mySQL help with simple query

sgtpepper

I am just starting to code in php and mysql. Here is my first problem....and I realize this should be easy to answer.

I have a mysql database with several tables. I have a query that looks like this

$query="SELECT * FROM listingsDB WHERE Title like '%Historic%'";

This will select all the listings that contain the word 'Historic' in the Title field. My question is, now how do I get it to display the rest of the info in the table for this query? For example:

Lets say the above query found 3 results. My table 'listingsDB' has several other fields....one is 'street'. How can I set it up so that the data in the 'street' field for the results of the query string, is displayed along with the results of the query string.

Thank you for the help....the complete code is below....

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM listingsDB WHERE Title like '%Historic%'";

$result=mysql_query($query) or die (mysql_error());
$num=mysql_numrows($result);

mysql_close();

$i=0;
while ($i < $num) {

$agent_name=mysql_result($result,$i,"Title");
echo "<b>$agent_name</b><br>";

++$i;
}

My simple

stolzyboy

$query="SELECT * FROM listingsDB WHERE Title like '%Historic%'";

$result=mysql_query($query) or die (mysql_error());

while ($row = mysql_fetch_array($result)) // this will grab the results from query
{
echo $row['fieldname'];
}

sgtpepper

Excellent! Thank you that seemed to do the trick....I have another question....again, probably simple but giving me a hard time.

My plan is to have different query's in this table.

What is the proper way to setup more than one query in my code?

Also, the output on my current code is difficult to arrange. How can I plave the query outputs in tables or how can I arrange the outputs where I want them to go on the page?

My full code looks like this:

<?
mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM listingsDB WHERE Title like '%Historic%'";

$result=mysql_query($query) or die (mysql_error());
$num=mysql_numrows($result);

while ($row = mysql_fetch_array($result)) // this will grab the results from query
{
echo $row['ID'];
}

mysql_close();

$i=0;
while ($i < $num) {

$agent_name=mysql_result($result,$i,"Title");
echo "<b>$agent_name</b><br>";
++$i;
}

stolzyboy

you can put whatever html or php you want in your while loop, if you want each new record in a different row of a table do something like

//php stuff
?>
<table>
<?
while ($row = mysql_fetch_array($result)) // this will grab the results from query 
{ 
?>
<table>
<tr>
<td>
<? echo $row['ID']; ?> 
</td>
<td>
<? echo $row['other_field_name']; ?>
</td>
</tr>
<?
}
?>
</table>
<? 
//more php stuff

sgtpepper

Thank you....this is working great.

The last pick of help I need now is with adding a second query.

Right now I have a query of:

$query="SELECT * FROM listingsDB WHERE Title like '%Historic%'";

Eventually I plan on replacing %Historic% with the output of a form. So, I am planning on having a form that will be checking different fields of the table. How can I setup another query to handle the other fields?

The goal is to have a form that checks multiple fields and displays the results that match the fields.

Full code now:

mysql_connect(localhost,$username,$password);
@mysql_select_db($database) or die( "Unable to select database");

$query="SELECT * FROM listingsDB WHERE Title like '%Historic%'";

$result=mysql_query($query) or die (mysql_error());
$num=mysql_numrows($result);
?>
<table>
<?
while ($row = mysql_fetch_array($result)) // this will grab the results from query
{
?>
<table>
<tr>
<td>
<? echo $row['ID']; ?>
</td>
<td>
<? echo $row['expiration']; ?>
</td>
</tr>
<?
}
?>
</table>
<?

mysql_close();

$i=0;
while ($i < $num) {

$agent_name=mysql_result($result,$i,"Title");
echo "<b>$agent_name</b><br>";
++$i;
}

kevinsequeira

as i understand it you want the user to choose values from a form which u will then use to run the sql query $query="SELECT * FROM listingsDB WHERE Title like '%Historic%'"; where you will replace Historic by what the user selects in the form ??
if thats the case then
1. have a form in which the you have a variable called $selected_value [ or whatever u like ]
2. then submit the form to the page where the query is run and change it to this

$query="SELECT * FROM listingsDB WHERE Title like '%".$selected_value."%'";

hope this is what you were looking for

reg
kevin

sgtpepper

yes, I already have that......

My question is....

I want the form to search more than one field in the table.

As it stands now...

$query="SELECT * FROM listingsDB WHERE Title like '%$search%'";

This query will take the results from the form....and query only the Title table.

But, I want to have other form elements that search other tables in the listingsDB but I am not sure how to setup the script to handle multiple queries.

So, my goal would be....lets say I have three textboxes in my form

1) box1:
2) box2:
3) box3:

I want box1 to query a specific field, box2 query another field, and box 3...yet another field.

The output will be the results of the three queries.

To put it in real working terms....

If box 1 represents a town - Box2 a state and box 3 a country
If the user inputs

box1 New York
box2 NY
box 3 USA

The results will be all listings for New York, NY USA

Hope I have explained this well....

kevinsequeira

how are the tables set up in your database ?
the listings that you want to display , do they have foreign key relationship to the town, state and country tables ??

eg
town table = id|town_name
.
.

18|New York

state table = id|state_name
.
.
25|NY

country table = id|country_name
.
.
.
4|USA

listing table = id|town_id|state_id|country_id|listing

.
.
.
36|18|25|4|some listing

$query = "select * from town, state, country, listing where town.id = listing.town_id AND state.id = listing.state_id AND country.id = listing.country_id AND town.name ='$box1' AND state.name='$box2' AND country.name = '$box3' "

reg
kevin

sgtpepper

Actually, it is one table....with multiple fields.

so, in my example...

town, city, state

are fields of the one table.

kevinsequeira

well that makes it easier

SELECT * from listing where town_name = '$box1' AND state_name = '$box2' AND country_name='box3';

SELECT * from listing where town_name like '%$box1%' AND state_name like '%$box2%' AND country_name like '%box3%';

reg
kevin

sgtpepper

Wow! That does look easy. Thank you!