BOOLEAN WHO ? (FROM WHERE Headbanger)

FreakingOUT · February 3, 2014

PHP 5.2.17

MySQL 5.5.14

I get the simple test one column data to display OK with the following script when it first loads, except that that the table displays before I can even enter a 'CALL' in the text input field.

<?php

require '/myserverpathhere/kqr_mysqli.php';

// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

// define variables and set to empty values

$call = "";

if ($_SERVER["REQUEST_METHOD"] == "POST")
{
$call = test_input($_POST["call"]);
}

function test_input($data)
{
$data = trim($data);
$data = stripslashes($data);
$data = htmlspecialchars($data);
return $data;
}

?>

<html>
<head><title></title>
</head>
<body>

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
CALL: <input type="text" name="call"> <input type="submit">
</form>
</body>
</html>

<?php

$sql = "SELECT * FROM qsolog";

$result = mysqli_query($con,$sql);

echo "<table border='1'>
<tr>
<th>CALL</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['call'] . "</td>";
echo "</tr>";
}
echo "</table>";

mysqli_close($con);
?>

In addition to this issue, with previous versions of the script I was unable to query with an actual 'CALL' without getting an error.

I tried substituting this in the $sql Query:

$sql = "SELECT * FROM qsolog WHERE call = '4F9HXB' ";

That is an actual 'CALL' the displays when the page loads as mentioned previously (only one 'CALL' in the test dB Table).

I get this error:

Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given <snip> on line 54

I've also tried this:

$sql = "SELECT * FROM qsolog WHERE call = '$call' ";

... but get the same error.

Even tried this:

$sql = "SELECT * FROM qsolog WHERE call = '$_POST[call]' ";

...yielding the same error.

And this:

$sql = "SELECT * FROM qsolog WHERE call = '$_POST['call']' ";

...with this error:

Parse error: syntax error, unexpected T_ENCAPSED_AND_WHITESPACE, expecting T_STRING or T_VARIABLE or T_NUM_STRING <snip> on line 44

My brain is FRIED trying to figure out what is wrong...even just using ONE table column and ONE record to K.I.S.S. ;-(

Any help is appreciated (and how to prevent the Table cells from displaying BEFORE I actually submit any 'CALL' Query).

Thanks very much.

-FreakingOUT

Barand · February 3, 2014

The error means your query failed. In this case because CALL is a mysql reserved word, so you need to enclose it in backticks.

$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";

Also you need to sanitize the post data with mysqli_real_escape_string() and not with htmlentities() prior to using it in the query. Or use a prepared statement.

Check data has been posted before attempting to query and output the table

FreakingOUT · February 3, 2014

The error means your query failed. In this case because CALL is a mysql reserved word, so you need to enclose it in backticks.
$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";
Also you need to sanitize the post data with mysqli_real_escape_string() and not with htmlentities() prior to using it in the query. Or use a prepared statement.

Check data has been posted before attempting to query and output the table

Thank you, Barand. I had tried backticks with 'call' before, but apparently NOT with the '$call' combination. That works.

I inserted this into the PHP:

$sql = "SELECT * FROM qsolog WHERE 'call' = '$call' ";

mysqli_real_escape_string($con,$call);

...but entering the valid CALL (guess I need to change that column name even though I was surprised it worked in the Table), yielded no output.

What am I doing wrong? For multiple column queries like WHERE 'call' = 'something' AND anothercolumn = 'something', do I need to use mysqli_real_escape_string($con,$call) for each one, or can I somehow combine all of them?

Thanks!

-FreakingOUT

FreakingOUT · February 3, 2014

Oooops:

mysqli_real_escape_string($con,$call);

That comes before the $sql = "SELECT ....... line:

===

$sql = "SELECT * FROM qsolog WHERE 'call' = '$call' ";

mysqli_real_escape_string($con,$call);

$result = mysqli_query($con,$sql);

echo "<table border='1'>
<tr>
<th>CALL</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['call'] . "</td>";
echo "</tr>";
}
echo "</table>";

===

FreakingOUT · February 3, 2014

Sorry, not enough sleep here. This is the sequence, but still not yielding output from the text input field query:

===

mysqli_real_escape_string($con,$call);

$sql = "SELECT * FROM qsolog WHERE 'call' = '$call' ";

$result = mysqli_query($con,$sql);

echo "<table border='1'>
<tr>
<th>CALL</th>
</tr>";

while($row = mysqli_fetch_array($result))
{
echo "<tr>";
echo "<td>" . $row['call'] . "</td>";
echo "</tr>";
}
echo "</table>";

===

-FreakingOUT

mac_gyver · February 3, 2014

a) if you are using the mysqli functions, you must use them for everything (you cannot mix in a mysql (no i) function.)

b) the mysql(i)_real_escape_string function returns the value that has been escaped. you must assign that returned value to a variable that then gets used in the query statement.

c) back-ticks ` are not the same as single-quotes '. it would be much better to rename your database column so that it doesn't use a reserved mysql keyword. back-ticks go around identifiers (database, table, and column names) when you use reserved keywords or other values that need special handling as identifiers. single-quotes go around literal string data inside a query statement.

Edited February 3, 2014 by mac_gyver

Barand · February 3, 2014



$call = mysqli_real_escape_string($con,$call);

$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";

$result = mysqli_query($con,$sql);


if (!$result) echo mysqli_error($con);


if (mysqli_num_rows($result) == 0 ) {

    echo "No records found<br>";

}

else {

   // display results

}

Edited February 3, 2014 by Barand

FreakingOUT · February 3, 2014

$call = mysqli_real_escape_string($con,$call);
$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";
$result = mysqli_query($con,$sql);

if (!$result) echo mysqli_error($con);

if (mysqli_num_rows($result) == 0 ) {
    echo "No records found<br>";
}
else {
   // display results
}

(Barand) I did this:

echo "<table border='1'>

<tr>

</tr>";

$call = mysqli_real_escape_string($con,$call);

$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";

$result = mysqli_query($con,$sql);

if (!$result) echo mysqli_error($con);

if (mysqli_num_rows($result) == 0 ) {

echo "No records found<br>";

}

else {

//while($row = mysqli_fetch_array($result))

// {

echo "<tr>";

echo "<td>" . $row['call'] . "</td>";

echo "</tr>";

}

echo "</table>";

Here's what happens:

1. When the page loaded, "the No records found" displayed BEFORE I entered text in the Query field.

2. When I did enter the text ("CALL"), the record did not display - only the <th>CALL</th> header.

(mac-gyver) OMG... I had never seen the `backtick` underneath the ~ key before (DUH!). Talk about an eye opener - Thanks!!!

-FreakingOUT

FreakingOUT · February 3, 2014

Ooops:

//while($row = mysqli_fetch_array($result)

changed back to...

while($row = mysqli_fetch_array($result)

... but still no results when submitting a text input query.

Baffling.

-FreakingOUT

mac_gyver · February 3, 2014

what do you see when you do a 'view source' in your browser of the resultant output?

FreakingOUT · February 3, 2014

what do you see when you do a 'view source' in your browser of the resultant output?

Miracle of Miracles - the search query now works, but when the page initially loads, the "No records found" still displays prematurely.

Here is the Brower source display:

===

<html>

</head>

<body>

CALL: <input type="text" name="call"> <input type="submit">

</form>

</body>

</html>

<tr>

</tr><tr><td>4F9HXB</td></tr></table>

===

Here is the revised php code (Thanks to you and Barand) that "almost works" except for the "No records found" issue:

<?php

require '/home/eric/domains/k6vva.com/kqr_mysqli.php';

// Check connection

if (mysqli_connect_errno())

{

echo "Failed to connect to MySQL: " . mysqli_connect_error();

}

// define variables and set to empty values

$call = "";

if ($_SERVER["REQUEST_METHOD"] == "POST")

{

$call = test_input($_POST["call"]);

}

function test_input($data)

{

$data = trim($data);

$data = stripslashes($data);

$data = htmlspecialchars($data);

return $data;

}

?>

<html>

</head>

<body>

CALL: <input type="text" name="call"> <input type="submit">

</form>

</body>

</html>

<?php

//mysqli_real_escape_string($con,$call);

//$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";

//$result = mysqli_query($con,$sql);

echo "<table border='1'>

<tr>

</tr>";

$call = mysqli_real_escape_string($con,$call);

$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";

$result = mysqli_query($con,$sql);

if (!$result) echo mysqli_error($con);

if (mysqli_num_rows($result) == 0 ) {

echo "No records found<br>";

}

else {

while($row = mysqli_fetch_array($result))

{

echo "<tr>";

echo "<td>" . $row['call'] . "</td>";

echo "</tr>";

}

echo "</table>";

}

//mysqli_close($con);

?>

Thanks,

-FreakingOUT

FreakingOUT · February 3, 2014

This is strange...

I added one more column header:

And one more column to display:

echo "<td>" . $rows['call'] . "</td>";
echo "<td>" . $rows['qsodate'] . "</td>";

... but now get absolutely no output displaying after the search query.

Any thoughts?

Thanks.

-FreakingOUT

FreakingOUT · February 3, 2014

FOUND THE PROBLEM...

echo "<td>" . $rows['call'] . "</td>";
echo "<td>" . $rows['qsodate'] . "</td>";

Should be:

echo "<td>" . $row['call'] . "</td>";
echo "<td>" . $row['qsodate'] . "</td>";

I had copied it from a previous version of the script ;-(

Please excuse the bandwidth.

Only the premature "No records found" issue remains.

-FreakingOUT

mac_gyver · February 3, 2014

you can get php to help you find things like the $rows variable by setting php's error_reporting to E_ALL and display_errors to ON (in your php.ini is the best place to set these.)

you no records found is due to a lack of program LOGIC in your code. when the code is (first) requested without a search term, what do you want the page to do? it's up to the LOGIC that you write to control what happens for that case.

FreakingOUT · February 3, 2014

I just tried this to see if "No records found" would ONLY display IF no records were actually found based upon a Valide Search entry.

The results display fine with the Valid 'CALL' entered, but if some other 'CALL' (not actually in the dB Table), then "No records found" does not show.

$call = mysqli_real_escape_string($con,$call);

$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";

$result = mysqli_query($con,$sql);

if (!$result) echo mysqli_error($con);

if (mysqli_num_rows($result) >= 0 ) {

while($row = mysqli_fetch_array($result))
{
echo "<tr>";

    echo "<td>" . $row['call'] . "</td>";
   echo "<td>" . $row['timeon'] . "</td>";

echo "</tr>";
}
echo "</table>";}
else {
if (mysqli_num_rows($result) == 0 ) {
    echo "No records found<br>";
}

}

At least I got rid of the "No records found" displaying as soon as the page loaded.

-FreakingOUT

FreakingOUT · February 3, 2014

you can get php to help you find things like the $rows variable by setting php's error_reporting to E_ALL and display_errors to ON (in your php.ini is the best place to set these.)

you no records found is due to a lack of program LOGIC in your code. when the code is (first) requested without a search term, what do you want the page to do? it's up to the LOGIC that you write to control what happens for that case.

The PHP is on my ISP's server - I don't think I have access to the php.ini file?

Now is just discovered that:

if (mysqli_num_rows($result) >= 0 ) {

...should have been >= 1

but that yields a new error.

I think I need to go on a lonnnnnnnng drive.

Everyone's efforts here have been greatly appreciated, but I need to go clear the head ;-(

Thanks,

-FreakingOUT

mac_gyver · February 3, 2014

the LOGIC conditions you just wrote are incorrect (by not posting a copy/paste solution for you to use, we are trying to get you to think like a programmer.) if there are zero rows, in the >= 0 test, you don't want the code to loop as there won't be anything to loop over. also, since the if(){} contains an equal to zero condition, the else{} won't ever be executed for the equal to zero condition.

when the search term is empty, what do you want your page and your code to do? you have do define this first, not just try an make the messages go away.

and please post your code in the forum using the forum's

bbcode tags (the edit form's <> button).

Edited February 3, 2014 by mac_gyver

mac_gyver · February 3, 2014

reply to your reply made while i was typing the above.

a) you should be learning php, developing and debugging php code on a local development system. apache/php/mysql can be installed on any current pc/laptop.

b) you likely have access to a local php.ini on your hosting server where you can put your own settings.

c) slow down, randomly trying things doesn't lead to solutions in programming. you must first define what you want, then write the code (see part of my reply above about what you want to do if the search term is empty.)

Edited February 3, 2014 by mac_gyver

FreakingOUT · February 3, 2014

Thanks for the advice, mac_gyver.

I will come back laterand RE-READ your admonitions, and try to chill out a bit here. Just tried something else (attempting to think somewhat 'logically', but that did not pan out - includinng >== 1 this time).

-FreakingOUT

FreakingOUT · February 3, 2014

P.S.

I know what I want the page to do:

1. When it first comes up, to have ONLY the text input box and 'SUBMIT QUERY' button.

2. If a 'CALL' is entered into the text field and the button pushed:

A. For a valid 'CALL' with a record (or more for that 'CALL') in the dB Table, to display the data in an HTML Table.

B. If the 'CALL' query is for one with NO records in the dB Table, the to display the "No records found' text

3. To never have the page load with the "No records found" text since no query has yet been submitted :^)

-FreakingOUT .. .and heading OUT into the sunshine and blue skies for a while !!!

Barand · February 3, 2014

In other words

Display form

if a call value was posted
    query table
    if no records
        display no records
    else
        display results
    end if
end if

FreakingOUT · February 4, 2014

(mac_gyver):

Umh, never made it on the lonnnnnng drive (a short one, but much later) as other 'stuff' came up ;-(

I've re-read all our helpful comments, and figured out how to NOT have the <th></th> part display until a bona fide 'CALL' search is done. Whew.

Emailed my ISP with a query about the php.ini file, and have been looking for a good PHP Editor. Any suggestions welcomed.

10 years ago I did have a local machine Apache/PHP/MySQL installation and that is on the "To Do" list as am just coming back 'into the fold' (albeit was still a 'NOOB' back then).

From what I can determine at this point, the easiest thing would be to have any 'results' (valid search or not) display on a 2nd page, but want to try and avoid that. Tried the 'No records found' code in several different positions, but just can not seem to crack this nut.

Apparently my 'logic' is flawed, but using this for the bona fide dB search part still seems to make sense:

if (mysqli_num_rows($result) >= 1 )

At this point, at least 2 of the 3 objectives are now being met on a single page. Ultimately, there will be 8 to 10 columns of data, and fortunately I know how to add these now .

Hopefully I'm posting the exisitng code properly this time!

Thanks again for your input.

-FreakingOUT

RE:

===

<?php

require '/myserverpath/kqr_mysqli.php';

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }


// define variables and set to empty values

$call = "";

if ($_SERVER["REQUEST_METHOD"] == "POST")
{
  $call = test_input($_POST["call"]);
}

function test_input($data)
{
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}

?>

<html>
<head><title></title>
</head>
<body>

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
CALL: <input type="text" name="call"> <input type="submit">
</form>
</body>
</html>

<?php

$call = mysqli_real_escape_string($con,$call);

$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";

$result = mysqli_query($con,$sql);

if (!$result) echo mysqli_error($con);

if (mysqli_num_rows($result) >= 1 ) {

echo "<table border='1'>
<tr>
<th>CALL</th>
<th>DATE</th>

</tr>"; 

while($row = mysqli_fetch_array($result))

{
  echo "<tr>";

	echo "<td>" . $row['call'] . "</td>";
	echo "<td>" . $row['qsodate'] . "</td>";

  echo "</tr>";
}

echo "</table>";

}

//if (mysqli_num_rows($result) == 0 ) {
//    echo "No records found<br>";
//}


mysqli_close($con);
?>

FreakingOUT · February 4, 2014

In other words

Display form

if a call value was posted
    query table
    if no records
        display no records
    else
        display results
    end if
end if

Yes... "almost":

Display form <--- (But  WITHOUT 'No Records found' showing on initial page load)

if a call value was posted
    query table
    if no records
        display no records
    else
        display results
    end if
end if

I shall keep at it here.

Thanks again for your input.

-FreakingOUT

FreakingOUT · February 4, 2014

> I shall keep at it here.

Logically (to me), this should work, but I still get the UNWANTED "No records found" displaying on initial page loading.

<?php

require '/myserverpath/kqr_mysqli.php';

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

// define variables and set to empty values

$call = "";

if ($_SERVER["REQUEST_METHOD"] == "POST")
{
  $call = test_input($_POST["call"]);
}

function test_input($data)
{
  $data = trim($data);
  $data = stripslashes($data);
  $data = htmlspecialchars($data);
  return $data;
}

?>

<html>
<head><title></title>
</head>
<body>

<form method="post" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>">
CALL: <input type="text" name="call"> <input type="submit">
</form>
</body>
</html>

<?php

$call = mysqli_real_escape_string($con,$call);

$sql = "SELECT * FROM qsolog WHERE `call` = '$call' ";

$result = mysqli_query($con,$sql);

if (!$result) echo mysqli_error($con);

if (mysqli_num_rows($result) == 0 ):
    echo "No records found<br>";
else:
if (mysqli_num_rows($result) >= 1 ):
echo "<div style ='font:11px Arial,tahoma,sans-serif;color:#ff0000'>";
echo "<table border='1' cellpadding='3' cellspacing='3'>
<tr>
<th>EXPD</th>
<th>IOTA</th>
<th>ISLAND NAME</th>
<th>CALL</th>
<th>DATE</th>
<th>UTC</th>
<th>BAND</th>
<th>MODE</th>
</tr>"; 
while($row = mysqli_fetch_array($result)) {
  echo "<tr>";
	echo "<td>" . $row['mycall'] . "</td>";
	echo "<td>" . $row['iota'] . "</td>";
	echo "<td>" . $row['islandname'] ."</td>";
	echo "<td>" . $row['call'] . "</td>";
	echo "<td>" . $row['qsodate'] . "</td>";
	echo "<td>" . $row['timeon'] . "</td>";
	echo "<td>" . $row['band'] . "</td>";
	echo "<td>" . $row['mode'] . "</td>";
}
endif;
echo "</table>";
echo "</div>";
endif;

mysqli_close($con);
?>

-FreakingOUT

jazzman1 · February 4, 2014

Where are you defined a $con variable as a first parameter to mysqli_real_escape_string() function?

$call = mysqli_real_escape_string($con,$call);

Sign In

BOOLEAN WHO ? (FROM WHERE Headbanger)

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information