Php unexplained results form mysql query

breeze351
breeze351 used Ask the Experts™
on
I'm doing a query with php.  The data doesn't exist in the table but mysql_num_rows is returning a count of "1".  Here's the code in question:

// Build Sql query & execute
$Work = str_replace(" ","",$Street);
$SqlString1 = "SELECT * FROM saka WHERE HUH LIKE '$Work%'";
echo "$SqlString1 <br>";
$Saka_File = mysql_query($SqlString1);
$count = mysql_num_rows($Saka_File);

// If no street name
echo "count = $count <br>";

The query is correct.  I'm not sure what this could be.

Also, I'm not sure if this is the correct forum.   I have a login page which displays correctly in Firefox but not in IE.  If you would like to take a look it's "mrbreeze.net".

Thanks
Glenn
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
I wouldn't use mysql_num_rows, I would use this:

if ($aka_File = mysql_query($SqlString1))

Ref.  http://stackoverflow.com/questions/3772791/mysql-num-rows-always-returns-1
Dave BaldwinFixer of Problems
Most Valuable Expert 2014

Commented:
Try putting a value in place of $Work in the SQL statement and see what happens.  I'm wondering if you ending up with a blank string that matches something in the database.

Your page looks the same in both IE8 and Firefox12.  But you haven't fixed the errors that were there the last time you posted.  So I fixed them for you.  I put the <form> tag where it belongs and fixed the image link.  And in the CSS I changed 'hissen' to 'hidden' like it should be.  You may find that those three fix your display problem.
mrbreeze.net.htm
lansco.css

Author

Commented:
I need to know the number of rows.  So you're solution doesn't work for me.
OWASP: Avoiding Hacker Tricks

Learn to build secure applications from the mindset of the hacker and avoid being exploited.

Commented:
Here is full-proof solution:

<?php
function get_rows ($table_and_query) {
        $total = mysql_query("SELECT COUNT(*) FROM $table_and_query");
        $total = mysql_fetch_array($total);
        return $total[0];
}
?>

Ref.  http://php.net/manual/en/function.mysql-num-rows.php

But, I would try to figure out why it's returning one row, since it should be returning FALSE if there were no rows returned.  I guess it depends if you have a different version of php, or if you're php needs updated.

Try running this code and seeing what happens:

// Build Sql query & execute
$Work = str_replace(" ","",$Street);
$SqlString1 = "SELECT * FROM saka WHERE HUH LIKE '$Work%'";
echo "$SqlString1 <br>";
$Saka_File = mysql_query($SqlString1);
$count = mysql_num_rows($Saka_File);

// If no street name
echo "count = $count <br>";
echo 'result = '.mysql_result($Saka_File, 0);
Most Valuable Expert 2011
Top Expert 2016
Commented:
@breeze351: Please take a little time to learn how PHP and MySQL play together.  This book is an excellent learning resource.
http://www.sitepoint.com/books/phpmysql4/

Not sure if you're using it, but if you're not, this is a great tool.
http://www.phpmyadmin.net/home_page/index.php

Here is an example that shows some of the basics.  You may want to learn about var_dump() since it will help you answer most of the questions you might have about what your data contains.  Example: If you retrieve the rows from your query results set and use var_dump() to print them out, you will be able to see what MySQL returned!
<?php // RAY_mysql_example.php
error_reporting(E_ALL);


// THE ABSOLUTE MINIMUM YOU MUST UNDERSTAND TO USE PHP AND MYSQL
// MAN PAGE: http://php.net/manual/en/ref.mysql.php
// MAN PAGE: http://php.net/manual/en/mysql.installation.php
// MAN PAGE: http://php.net/manual/en/function.mysql-connect.php
// MAN PAGE: http://php.net/manual/en/function.mysql-select-db.php
// MAN PAGE: http://php.net/manual/en/function.mysql-real-escape-string.php
// MAN PAGE: http://php.net/manual/en/function.mysql-query.php
// MAN PAGE: http://php.net/manual/en/function.mysql-errno.php
// MAN PAGE: http://php.net/manual/en/function.mysql-error.php
// MAN PAGE: http://php.net/manual/en/function.mysql-num-rows.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-assoc.php
// MAN PAGE: http://php.net/manual/en/function.mysql-fetch-array.php
// MAN PAGE: http://php.net/manual/en/function.mysql-insert-id.php



// DATABASE CONNECTION AND SELECTION VARIABLES - GET THESE FROM YOUR HOSTING COMPANY
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $err <br/>";
}

// SELECT THE MYSQL DATA BASE
if (!mysql_select_db($db_name, $db_connection))
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $err <br/>";
    die('NO DATA BASE');
}
// IF THE SCRIPT GETS THIS FAR IT CAN DO QUERIES




// ESCAPE ALL DATA FIELDS BEFORE USE IN MYSQL QUERIES
$safe_username = mysql_real_escape_string($_POST["username"]);




// CREATE AND SEND A SELECT QUERY AND TEST THE RESULTS
$sql = "SELECT id FROM my_table WHERE username='$safe_username'";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, SHOW THE ERROR
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/> $sql <br/>";
    die($err);
}
// IF WE GET THIS FAR, THE QUERY SUCCEEDED AND WE HAVE A RESOURCE-ID IN $res SO WE CAN NOW USE $res IN OTHER MYSQL FUNCTIONS




// DETERMINE HOW MANY ROWS OF RESULTS WE GOT
$num = mysql_num_rows($res);
$fmt = number_format($num);
if (!$num)
{
    echo "<br/>QUERY FOUND NO DATA: ";
    echo "<br/> $sql <br/>";
}
else
{
    echo "<br/>QUERY FOUND $fmt ROWS OF DATA ";
    echo "<br/> $sql <br/>";
}




// ITERATE OVER THE RESULTS SET TO SHOW WHAT WE FOUND
while ($row = mysql_fetch_assoc($res))
{
    // ROW BY ROW PROCESSING IS DONE HERE
    var_dump($row);
}




// ANOTHER WAY OF DETERMINING HOW MANY ROWS WE HAVE IN A TABLE
$sql = "SELECT COUNT(*) FROM my_table";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/>$sql <br/>";
    die($err);
}
// GET THE RESULTS SET ROW IN AN ARRAY WITH A NUMERIC INDEX - POSITION ZERO IS THE COUNT
$row = mysql_fetch_array($res, MYSQL_NUM);
$num = $row[0];
$fmt = number_format($num);
echo "<br/>THERE ARE $fmt ROWS IN THE TABLE";




// MAKING AN INSERT QUERY AND TESTING THE RESULTS
$sql = "INSERT INTO my_table (username) VALUES ('$safe_username')";
$res = mysql_query($sql);

// IF mysql_query() RETURNS FALSE, GET THE ERROR REASONS
if (!$res)
{
    $err = mysql_errno() . ' ' . mysql_error();
    echo "<br/>QUERY FAIL: ";
    echo "<br/> $sql <br/>";
    die($err);
}

// GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED - PER THE DB CONNECTION
$id  = mysql_insert_id($db_connection);
echo "<br/>YOU JUST INSERTED A RECORD WITH AUTO_INCREMENT ID = $id";

Open in new window

HTH, and best of luck, ~Ray

Author

Commented:
To EMB01:
I tried the code you suggested at the end of your message.  This is what I got!!:

SELECT * FROM saka WHERE HUH LIKE 'FRED%'
count = 1
result = FREDERICKDOUGLASSB

The table name saka stands for "Street Also Know As".   I can assure you that in Manhattan there is no "FREDERICKDOUGLAS..." in this table.   I have no idea where this data is coming from.  

To Ray:
Thanks for the lead.  My experience was using php with ODBC.  I have several sites using ODBC that are very similar to what I'm trying to do now.  How hard could be this be?  I already have the logic!  

Glenn
Saka-from-query-from-hosting24

Commented:
The computer isn't just making up "FREDERICKDOUGLASSB" so it must be coming from somewhere.  There simply must be an entry in your database called "FREDERICKDOUGLASSB."  Have you checked?  Thanks.

Author

Commented:
Yes I did. Look at the attached print screen.

Commented:
For some reason I can't open your file (Saka-from-query-from-hosting24).

Author

Commented:
See if this works better.
saka-query.doc

Commented:
You should just attach a .GIF.  But, that worked.  So, from the image, "FREDERICKDOUGLASSB" was being returned from the query because it's in the database!  So, your query accurately returns one result (the correct result).  Let me know if I am misunderstanding you.
Most Valuable Expert 2011
Top Expert 2016

Commented:
Using phpMyAdmin you can run any query you want in a "test mode" and see the results immediately.  It helps remove mysteries like this one!

Author

Commented:
No you have it right.  

I'm going to kill my partner on this deal.  He updates the data from the client and doesn't tell me (so you can tell the client's data sucks, as I said before there is no "Fred.." street in Manhattan).  I ran this query on Monday and there were no results.  I didn't even look at the new query because I assumed the data was the same.

I'm sorry for wasting your time.

Thanks
Glenn

Author

Commented:
I've requested that this question be closed as follows:

Accepted answer: 0 points for breeze351's comment #38003642

for the following reason:

My bad, didn't know someone else updated the data.

Commented:
I recommend choosing a solution since, technically, the solution was reached.  Glad you found out what the problem was, after all.  Thanks.
Most Valuable Expert 2011
Top Expert 2016

Commented:
@EMB01:
I recommend choosing a solution...
Yep!  Couldn't agree more.

And I recommend getting a copy of phpMyAdmin, installing it, and learning to use it frequently to look at the data.  It would have saved hours, if not days, on this problem.  And since everything we do in information technology is all about transforming data from one kind to another, it's not an overstatement to say that data visualization is the single most important thing a software developer can do.

Commented:
3) Accept one or more Expert posts as the answer

ID#:  37994978

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial