Stuck on this

[djdubuque]

OK, I'm trying to query my dB with the following php. It comes back with nothing.
One thing is before I made some changes it worked but it didn't, what I mean is that the query came back but with all the records not the one I had in the search box in my HTML file. So I tweaked it again and this time it only showed the first record, and it wasn't the one I was searching for.

Any help?

<?php


//open dB 
include 'connect.php';

// Retrieve the data from the "EN" table
if(isset($_POST['call_sign'])) {
$result = mysql_query "SELECT * FROM EN WHERE 'call_sign' '%".$searchterm."%'";
if (!$result){
die('Invalid query: ' . mysql_error());
}


// store the record of the "EN" table into $row
$row = mysql_fetch_array($result);

// Print out the contents of the entry 

echo "Call Sign :{$row['call_sign']} <br>" ;
echo "First Name : {$row['first_name']} <br>";
echo "Last Name : {$row['last_name']} <br>";
echo "Address : {$row['street_address']}<br>";
echo "PO Box : {$row['po_box']}<br>";
echo "City :  {$row['city']}<br>";
echo "State : {$row['state']}<br>";
echo "Zip Code : {$row['zip_code']}<br>";


/*free database*/
$result->free();
$dbconn->close();

?>

[Kudose]

Try

SELECT columns FROM EN WHERE call_sign LIKE '%something%'

Also, you are only getting one result because you are not iterating the returned rows. You are only showing the data for the first result returned.

i.e.

while($row = mysql_fetch_assoc($resourceLink))
  echo $row['call_sign'];

You should also read about data validation and SQL injection attacks to secure your code.

[djdubuque]

Kudose;10938608 wrote:

Try

SELECT columns FROM EN WHERE call_sign LIKE '%something%'

Also, you are only getting one result because you are not iterating the returned rows. You are only showing the data for the first result returned.

i.e.

while($row = mysql_fetch_assoc($resourceLink))
  echo $row['call_sign'];

You should also read about data validation and SQL injection attacks to secure your code.

I tried your inputs but not change. As far as the security, I'll do that once I learn how to gather my queries...😃

Could you suggest maybe a rewrite? With something you think it should look like. As you can see I'm a knob.😕

[halojoy]

what is $searchterm ??
i can not see where it gets its value

[djdubuque]

halojoy;10938614 wrote:

what is $searchterm ??
i can not see where it gets its value

I assumed that it was for a search box that you would use to enter your search criteria.

[djdubuque]

I think if you saw the HTML page it would help some.

callsign.htm

[Kudose]

This is untested.

<?php

  // function to escape text
  // it mainly just escapes quotes and slashes 
  // that are used in SQL injection attacks
  /**
   * Function to escape data being inserted into MySQL
   * 
   * @param mixed $mixed The string or array to escape
   * @return mixed The escaped data
   */
  function escape($mixed){
    if(empty($mixed))
      return false;
    elseif(!is_array($mixed))
      return mysql_real_escape_string($mixed);
    else 
      foreach($mixed as $key => $val)
        $mixed[$key] = escape($val);
    return $mixed;
  }

  require_once('connect.php');

  if(!empty($_POST['call_sign'])){
    $query = mysql_query("SELECT * FROM `EN` WHERE `call_sign` LIKE '%".escape($_POST['call_sign'])."%'");
    while(($row = mysql_fetch_assoc($query)) !== FALSE)
      print_r($row);
  }

  mysql_close();

[halojoy]

<form name="callsignsearch" action="search.php" method="GET">

Callsign Search: <input type="text" name="call_sign" size=8 maxsize=8/>
Zipcode Search: <input type="text" name="zip_code" size=5 maxsize=5/>

<input type="submit" value="Go!">
</form>

So, you have 2 search fields: "call_sign" and "zip_code"

What happens if people submit BOTH ....
What would the search be?

And you have method="GET"
But your script checks for $_POST['call_sign']

[djdubuque]

halojoy;10938620 wrote:

<form name="callsignsearch" action="search.php" method="GET">

Callsign Search: <input type="text" name="call_sign" size=8 maxsize=8/>
Zipcode Search: <input type="text" name="zip_code" size=5 maxsize=5/>

<input type="submit" value="Go!">
</form>

So, you have 2 search fields: "call_sign" and "zip_code"

What happens if people submit BOTH ....
What would the search be?

And you have method="GET"
But your script checks for $_POST['call_sign']

If both are used then the search will check to see if the query has both if not then no match, but if they use one or the other then 1-call sign will come up with that var information which is all the data from the fields I show in the above. 2- If just the zip is used it will pull all call signs within that zip code.

[djdubuque]

I think you asked me to try it like this correct? If so the data came back empty.

<?php

 // function to escape text
 // it mainly just escapes quotes and slashes
 // that are used in SQL injection attacks
 /**
  * Function to escape data being inserted into MySQL
  * @param mixed $mixed The string or array to escape
  * @return mixed The escaped data
  */
 function escape($mixed){
   if(empty($mixed))
     return false;
   elseif(!is_array($mixed))
     return mysql_real_escape_string($mixed);
   else
     foreach($mixed as $key => $val)
       $mixed[$key] = escape($val);
   return $mixed;
 }

 require_once('connect.php');

 if(!empty($_POST['call_sign'])){
   $query = mysql_query("SELECT * FROM `EN` WHERE `call_sign` LIKE '%".escape($_POST['call_sign'])."%'");
   while(($row = mysql_fetch_assoc($query) !== FALSE))
     print_r($row);
 }

// store the record of the "EN" table into $row
$row = mysql_fetch_array($result);

// Print out the contents of the entry 

echo "Call Sign :{$row['call_sign']} <br>" ;
echo "First Name : {$row['first_name']} <br>";
echo "Last Name : {$row['last_name']} <br>";
echo "Address : {$row['street_address']}<br>";
echo "PO Box : {$row['po_box']}<br>";
echo "City :  {$row['city']}<br>";
echo "State : {$row['state']}<br>";
echo "Zip Code : {$row['zip_code']}<br>";

 mysql_close();
?>

[Kudose]

halojoy;10938620 wrote:

And you have method="GET"
But your script checks for $_POST['call_sign']

Either change $POST to $GET in the PHP or change your form method to POST

[djdubuque]

Kudose;10938644 wrote:

Either change $POST to $GET in the PHP or change your form method to POST

I tried both and still no data. But it did add the 1 in this.

1Call Sign :
First Name :
Last Name :
Address :
PO Box :
City :
State :
Zip Code : 

[Kudose]

What happens with this poorly constructed and proof of concept code?

<?php

  // function to escape text
  // it mainly just escapes quotes and slashes
  // that are used in SQL injection attacks
  /**
   * Function to escape data being inserted into MySQL
   *
   * @param mixed $mixed The string or array to escape
   * @return mixed The escaped data
   */
  function escape($mixed){
    if(empty($mixed))
      return false;
    elseif(!is_array($mixed))
      return mysql_real_escape_string($mixed);
    else
      foreach($mixed as $key => $val)
        $mixed[$key] = escape($val);
    return $mixed;
  }

  require_once('connect.php');

  if(!empty($_POST['call_sign'])){
    $query = mysql_query("SELECT * FROM `EN` WHERE `call_sign` LIKE '%".escape($_POST['call_sign'])."%'");
    while(($row = mysql_fetch_assoc($query)) !== FALSE)
      print_r($row);
  } else {
    ?>
    <html>
      <body>
        <form method="post">
          Callsign: <input type="text" value="" name="call_sign" /><br />
          Zip Code: <input type="text" value="" name="zip_code" /><br />
          <input type="submit" value="Go!" />    

        </form>
      </body>
    </html>
    <?php
  }

  mysql_close(); 

[djdubuque]

It will spit this out.

Array ( [record_type] => EN [unique_system_identifier] => 537865 [call_sign] => KE4IAP [licensee_id] => L00205107 [entity_name] => DUBUQUE, DONALD J [first_name] => DONALD [mi] => J [last_name] => DUBUQUE [street_address] => 5799 ROCKCLIFF LN [city] => WOODBRIDGE [state] => VA [zip_code] => 22193 [po_box] => [attention_line] => )

[halojoy]

Now you can put in your print out of values

<?php

  // function to escape text
  // it mainly just escapes quotes and slashes
  // that are used in SQL injection attacks
  /**
   * Function to escape data being inserted into MySQL
   *
   * @param mixed $mixed The string or array to escape
   * @return mixed The escaped data
   */
  function escape($mixed){
    if(empty($mixed))
      return false;
    elseif(!is_array($mixed))
      return mysql_real_escape_string($mixed);
    else
      foreach($mixed as $key => $val)
        $mixed[$key] = escape($val);
    return $mixed;
  }

  require_once('connect.php');

  if(!empty($_POST['call_sign'])){
    $query = mysql_query("SELECT * FROM `EN` WHERE `call_sign` LIKE '%".escape($_POST['call_sign'])."%'");
    while(($row = mysql_fetch_assoc($query)) !== FALSE)

// Print out the contents of the entry

echo "Call Sign :{$row['call_sign']} <br>" ;
echo "First Name : {$row['first_name']} <br>";
echo "Last Name : {$row['last_name']} <br>";
echo "Address : {$row['street_address']}<br>";
echo "PO Box : {$row['po_box']}<br>";
echo "City :  {$row['city']}<br>";
echo "State : {$row['state']}<br>";
echo "Zip Code : {$row['zip_code']}<br>";

  } else {
    ?>
    <html>
      <body>
        <form method="post">
          Callsign: <input type="text" value="" name="call_sign" /><br />
          Zip Code: <input type="text" value="" name="zip_code" /><br />
          <input type="submit" value="Go!" />    

        </form>
      </body>
    </html>
    <?php
  }

  mysql_close(); 

[djdubuque]

That one when adding the print will only give the first row (callsign) all the rest are empty. But if I remove the print it will give the one I sent before with the arrays.

[halojoy]

djdubuque;10938715 wrote:

That one when adding the print will only give the first row (callsign) all the rest are empty. But if I remove the print it will give the one I sent before with the arrays.

yes. We need 2 brackets around the print:

<?php

  function escape($mixed){
    if(empty($mixed))
      return false;
    elseif(!is_array($mixed))
      return mysql_real_escape_string($mixed);
    else
      foreach($mixed as $key => $val)
        $mixed[$key] = escape($val);
    return $mixed;
  }

  require_once('connect.php');

  if(!empty($_POST['call_sign'])){
    $query = mysql_query("SELECT * FROM `EN` WHERE `call_sign` LIKE '%".escape($_POST['call_sign'])."%'");
    while(($row = mysql_fetch_assoc($query)) !== FALSE)
    {
    // Print out the contents of the entry
    echo "Call Sign :{$row['call_sign']} <br>" ;
    echo "First Name : {$row['first_name']} <br>";
    echo "Last Name : {$row['last_name']} <br>";
    echo "Address : {$row['street_address']}<br>";
    echo "PO Box : {$row['po_box']}<br>";
    echo "City :  {$row['city']}<br>";
    echo "State : {$row['state']}<br>";
    echo "Zip Code : {$row['zip_code']}<br>";
    }

  } else {
    ?>
    <html>
      <body>
        <form method="post">
          Callsign: <input type="text" value="" name="call_sign" /><br />
          Zip Code: <input type="text" value="" name="zip_code" /><br />
          <input type="submit" value="Go!" />    

        </form>
      </body>
    </html>
    <?php
  }

  mysql_close()

[djdubuque]

Perfect, Now to try and bring in my precode to have it look the way it did. I should only have to place this

<title>Ham Radio callsign search engine for ham radio operators.</title> 

<META NAME="Keywords" CONTENT="ham radio, callsign search engine!">
<META NAME="KEYWORDS" CONTENT="amateur, radio, amateur radio, ham, ham radio, arrl, short wave, shortwave, hobby, electronic, electronics, education, learning, scout, scouting, school, k12, emergency, communication, communications, telecommunication, books, magazines, software, QST, QEX, NCJ, Radio Designer, DXCC, contest, QSL, W1AW, repeaters, VHF, UHF, HF, microwave, license, club, public service, awards, certificates, volunteers, newsletter, regulations, FCC, technical, SAREX, space shuttle, projects, membership, member, VEC, exams, tests, foundation, scholarships">

<META http-equiv="Pragma" content="no-cache"> 



<script language="JavaScript" type="text/javascript">
function call_focus()
{
document.callsignsearch.call.value = "";
document.callsignsearch.call.focus();
}
</script>


<style type="text/css">
<!--
h1 {font-size: 50pt}
-->
</style>


<p>
<center>
<body  onload="call_focus()" bgcolor="white" leftmargin="0" rightmargin="0" marginheight="0" marginwidth="0" topmargin="0" text="black" link="blue" vlink="purple" alink="blue">
<a name="top"></a></p>

<!-- news -->
<table border="1" width="540" cellspacing="0" cellpadding="4">
<tr bgcolor="black">
<td><font face="arial" size="3">
<center>
<font color="white" face="arial" size="3"><b>FCC Amateur Radio Callsign Database Search </b></font><font face="arial" size="3"></b>
</center>


</font>
</td>
</tr>
<tr>
<td> 

  <center>


<p ALIGN=CENTER STYLE="margin-bottom: 0cm"><FONT FACE="Verdana, sans-serif"><FONT SIZE=3><b>HamWave.com Callsign Search</b><br>
<FONT SIZE=2>Powered by HamWave.com
<br>

into your code within the html and body tags right?