.PHP MySQL Query to HTML

bdenny20 · 02-04-2013, 05:24 PM

So, this is an offshoot to my little ASP.NET experiment. That's still going strong, but I'm working two angles to compare methods.

Here's where I'm at. For starters,

Code:

<html>
<body>
<form name="form" action="2nd.php" method="get">
  <input type="text" name="q" />
  <input type="submit" name="Submit" value="Search" />
</form>
</body>
</html>

Plain Jane, no styling form. Search bar and a submit button.

Here's "2nd" (As in, my 2nd attempt writing this.)

Code:

<html>
<body>
<?php
$username="root";
$password="password";
$database="airports";

mysql_connect('localhost',$username);
@mysql_select_db($database) or die( "Unable to select database");
$query = $_GET['q'];
$raw="SELECT * FROM restaurants where ICAO like '$query'";
$result=mysql_query($raw);

$num=mysql_numrows ($result);

mysql_close();
?>
<?php
echo ("You searched for: ");
    echo ($query)
?>
<table>
<tr>
<td>Restuarant Name</td>
<td>Description</td>
</tr>
<?php
$i=0;
while ($i < $num) {

$f1=mysql_result($result,$i,"ICAO");
$f3=mysql_result($result,$i,"RESTAURANTNAME");
$f4=mysql_result($result,$i,"DESCRIPTION");
?>
<tr>
<td><?php echo $f3; ?></td>
<td><?php echo $f4; ?></td>
</tr>
<?php
$i++;
}
?>
</table>
</body>
</html>

This was originally a demonstration code on how to query a MySQL database with PHP that I tweaked a little so that it only showed the entry that matches what was entered in the search bar.

What I'm trying to do is create a layout page of sorts that has a bunch of "blanks" for lack of a better word that are replaced with different parts of the query result.

I tried mixing and matching HTML into the code, reading online how to do it, utilizing quotes and all, but I can not get it work properly. The database is incomplete, there are a few more columns that need to be added.

The echo in the beginning is the closest I could come to creating a layout type feel, although what is echoed is not part of the result, but it simply restates what was typed in. I need that in the end, so that's good, but I can't mix the result into HTML. Any good tutorials on this would be great, I'm looking too.

Thanks in advance, as always.
Bill

P.S.-
Please, if in any way what I'm saying doesn't make sense, inform me. I'll try to clarify with a neat-o picture. I'm a MSPaint Wiz, and I can dandy you up with the sweet .bmp I'm visualizing in my head. It may help, it may make you wonder why I haven't considered an art major.

Old Pedant · 02-05-2013, 01:59 AM

I don't think your code is the best/most efficient way to do this in PHP, but it looks generally like it should work. Except I think you need to kill that mysql_close(). Not only is it deprecated, it's in the wrong place.

Oh, WTH. I don't use PHP. But since nobody else has answered:

Code:

<html>
<body>
<?php
$query = $_GET['q'];
if ( ! isset($query) || $query = "" )
{
    echo "Sorry, you didn't ask for anything, so I don't know what you want.";
    exit( );
}

$username="root";
$password="password";
$database="airports";

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

' do not use LIKE in a query unless you also use wild card(s) in the match
' AND you need to protect against SQL injection
' AND you should not use SELECT * -- select only the fields you will use 
$raw="SELECT restaurant, description FROM restaurants where ICAO = '" . mysql_real_escape_string($query) . "'";
$result=mysql_query($raw) or die( mysql_error( ) );
?>
<h3>You searched for <?php echo $query; ?></h3>
<table>
<tr>
    <td>Restaurant Name</td>
    <td>Description</td>
</tr>
<?php
while ($row = mysql_fetch_assoc($result) )
{
?>
<tr>
    <td><?php echo $row["restaurant"]; ?></td>
    <td><?php echo $row["description"]; ?></td>
</tr>
<?php
}
mysql_close();
?>
</table>
</body>
</html>

So maybe you do need to show that diagram?

bdenny20 · 02-05-2013, 03:59 PM

Mr. Pedant,
You rock. I figured my example wouldn't be exactly cutting edge in terms of efficiency, at least your criticism of it indicates I'm slowly getting better. To boot too, you gave me exactly what I needed, the more correct way to go about things. I have some homework to do, you've introduced a few characters I'm not familiar with, but I will soon.

Now, about that picture. It's not super fancy, but maybe it can give you an idea of what I'm trying to do. The one on the left is basically what I'm picturing in my head as a layout page, the one on the right is kind of what it looks like when the query pulls information and plops in its place.

So, items in the left in quotes, are where I'd like to stick a variable that corresponds to the correct value in the search function, with it sitting in the HTML, it could then be subjected to the CSS formatting.

Does this help? I'm just trying to take raw data from a search, and make it pretty and presentable.

In the meantime, I will educate myself on your revised version of the query.

Thanks,
Bill

Old Pedant · 02-05-2013, 07:22 PM

I don't think you really need much more from me. And as I said, I don't use PHP, so I'm likely not the best person to ask.

Clearly you need to make two SQL queries per HTML page: One to get the airport info (runway length, full airport name, etc., etc.) and then a separate one for the restaurants.

And of course the one for the airport info will *NOT* be in a while loop, since there will be only one record, so you probably want to just do

Code:

if ( ! $result ) 
{
     echo "Could not find ICAO designation $query";
     exit( );
}
$row = mysql_fetch_assoc( $result );
if ( ! $row ) 
{
    echo "?? what went wrong?  got result but no data?";
    exit( );
}
... use $row["fieldNames"] to display airport info ...