how to connect to mysql and do a simple query

[joefox]

Ok, dumb question...

I just wanted to have 1 box, that searches the "zip code" filed in my database, and it will search and dispaly results of that, if records are found.

Can anyone give me an idea on how to do this?

ive looked all over online, and there are way to many examples, and most of them dont make sense.

Thanks

[penagate]

This is a simple example:

<?php
  header('Content-type: text/plain; charset=UTF-8');

  $db = mysql_connect($hostname, $username, $password);

  if ($db) {
    mysql_select_db('DatabaseName', $db);

    $result_set = mysql_query('SELECT zip_code FROM table_name', $db);

    if (is_resource($result_set)) {
      while ($row = mysql_fetch_assoc($result_set))
        echo $row['zip_code']."\n";
    }
    else
      echo 'Query failed: '.mysql_error($db);
  }
  else
    echo 'Connection failed: '.mysql_error($db);
?>

In a production environment, you shouldn't use echo() to display all of the output, but rather wrap the PHP around your HTML:

<h1>Zip Codes</h1>
<ol>
  <?php while ($row = mysql_fetch_assoc($result_set)): ?>
    <li><?php echo $row['zip_code'] ?></li>
  <?php endwhile; ?>
</ol>

And once you have grasped this you should then move on to a data access library such as PDO or MySQLi, which offer parameterised queries. These are essential for any queries that use user input.

[joefox]

I was thinking... is there a way for it to check how many results are found?
Like if there is 1 result, have it do a redirect to a page, an if there are more then 1 result, then to kick out the results?

Also, where do i put the data for $databaseuser etc? Where do i define those, in a connection.php file?

I made a sameple a while back where i used another file, and then included that into my sheet taht was doing the querying.

thanks for all your help.

[joefox]

I was thinking... is there a way for it to check how many results are found?
Like if there is 1 result, have it do a redirect to a page, an if there are more then 1 result, then to kick out the results?

Also, where do i put the data for $databaseuser etc? Where do i define those, in a connection.php file?

I made a sameple a while back where i used another file, and then included that into my sheet taht was doing the querying.

thanks for all your help.

[TheBigB]

wouldn't it be easier to formulate the query like this?

"SELECT zip_code FROM table_name WHERE zip_code LIKE $input_zip_code" 

this would return 1 result or more, for which you could use the mysql_num_rows() function to find the number of results

[dclamp]

here is how you check how many rows you queried:

$sql = "SELECT `col` FROM `table` WHERE `something`='test'";
$query = mysql_query($sql);
$num = mysql_num_rows($query); //HERE IS WHERE IT GETS THE COUNT
if ($num == "1") {
     //IT IS EQUAL TO 1 ROW
     header("Location: newpage.php");
} else {
     //IT IS GREATER THEN, OR LESS THEN 1
     header("Location: otherpage.php");
} 

[joefox]

so if i have the rows: zip code, bus_name_, info1, and info2, zipcode, address, how can i displays those lines? on sepreate lines like

Joe's Busniess
Address, zip
notes1

thanks again everyone.

[TheBigB]

$sql = "SELECT * FROM table_name WHERE zip_code LIKE $input_zip_code'";
$query = mysql_query($sql);
$num = mysql_num_rows($query); //HERE IS WHERE IT GETS THE COUNT
if ($num == "1") {
     $row = mysql_fetch_assoc($query);
     $info1 = $row['columnName'];
     //if you repeat this for each column you need, you have it all sorted out in variables.
} else {
     //IT IS GREATER THEN, OR LESS THEN 1
     header("Location: otherpage.php");
} 

[joefox]

is there a way to connect with a gui interface to my server that is hosted with maxdedicated?
ive always used a gui for mysql on my local machine, but never on a hosted server

[joefox]

where do i define databaseuser etc. at?

[dclamp]

well if your server has cpanel, then you can access it from there, other wise you need to contact your host for that information. My guess would be through ssh connection

[TheBigB]

$db = mysql_connect($hostname, $username, $password); 
mysql_select_db('DatabaseName'); 

[TheBigB]

you might btw want to take a look at this site
http://www.php-mysql-tutorial.com/
the coding is pretty well explained

[joefox]

i wanted to make a page where when you go to it, you can put in a value into a text box, and then hit search, and then it searches, and displays the data...?

[dclamp]

well thats what pena's code does, you just need to add a few more things. Do you want to use POST data or GET data for your search?