MySQL查询不返回任何结果

I know that this type of question has been asked before. But it most probably is something to do with the query itself. right now the whole thing is php tagged. and this is where i think I am going wrong. This is a perfectly legit phpmyadmin query and returns desired results:

SELECT sm.stockid, (SELECT price FROM prices WHERE stockid = sm.stockid AND typeabbrev = 'RT') AS RT, (SELECT price FROM prices WHERE stockid = sm.stockid AND typeabbrev = 'TR') AS TR, (SELECT price FROM prices WHERE stockid = sm.stockid AND typeabbrev = 'FL') AS FL, (SELECT price FROM prices WHERE stockid = sm.stockid AND typeabbrev = 'DE') AS DE FROM stockmaster sm

This is the format I have it in:

$query = "SELECT sm.stockid, (SELECT price FROM prices WHERE stockid = sm.stockid AND typeabbrev = 'RT') AS RT, (SELECT price FROM prices WHERE stockid = sm.stockid AND typeabbrev = 'TR') AS TR, (SELECT price FROM prices WHERE stockid = sm.stockid AND typeabbrev = 'FL') AS FL, (SELECT price FROM prices WHERE stockid = sm.stockid AND typeabbrev = 'DE') AS DE FROM stockmaster sm";

This block of code though prints nothing:

$result = mysqli_query($db, $query);    
$recordCount = mysqli_num_rows($result);
echo $recordCount; 

But in phpmyadmin the query returns the following:

I probably need to escape the single quotes or redo my php tagging. Thank you in advance for any help.

You should fetch data using while loop.Try this:

$result = mysqli_query($db, $query);    
$recordCount = mysqli_num_rows($result);
if($recordCount > 0){
   while($row = mysqli_fetch_array($result)){
      print_r($row);
   }
}

In phpmyadmin running your query is giving you the result hence the table like output. But in php what you are doing is simply getting the number of rows in your result and echoing it.

If you want to print the table like output using php you need do to something like this:

$result = mysqli_query($db, $query);   
$recordCount = mysqli_num_rows($result);
while($row = mysqli_fetch_array($result)){
    echo $row;
}

NOTE: You can skip the mysqli_num_rows if you don't want to use the number of rows of your result in your code.

Also I noticed that your query in phpmyadmin and php code is not exactly same. Did you try it with the exact same queries? Because the echo $recordCount should have atleast given you the number of rows.

I don't know if this helps but I have experienced Problems like this before and solved it by using the object-oriented mysqli methods. Try to make a new mysqli - object and run the query with $mysqli->query($sql); Sometimes this solved it for me.

I hope this helps. Try using $recordCount = $result->num_rows; instead of $recordCount = mysqli_num_rows($result); It should work as mysqli_result object is giving num_rows.

Seems odd that the query runs OK in phpmyadmin but not in your app - might be worth trying a fully quoted and escaped version of the query perhaps.

$dbhost =   'localhost';
$dbuser =   'root'; 
$dbpwd  =   'xxx'; 
$dbname =   'xxx';
$db =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );


$sql="SELECT 
    sm.`stockid`, 
    ( SELECT `price` FROM `prices` WHERE `stockid` = sm.`stockid` AND `typeabbrev` = 'RT' ) AS 'RT', 
    ( SELECT `price` FROM `prices` WHERE `stockid` = sm.`stockid` AND `typeabbrev` = 'TR' ) AS 'TR', 
    ( SELECT `price` FROM `prices` WHERE `stockid` = sm.`stockid` AND `typeabbrev` = 'FL' ) AS 'FL', 
    ( SELECT `price` FROM `prices` WHERE `stockid` = sm.`stockid` AND `typeabbrev` = 'DE' ) AS 'DE' 
    FROM `stockmaster` sm";

$result = $db->query( $sql );
$rows = intval( $result->num_rows );

$result->close();
$db->close();

printf( "Rows: %d", $rows );

To test this for myself I built a couple of new tables in a test database and populated with dummy records

$a=array('RT','TR','FL','DE');
$r=range( 0.1, 1, 0.01 );

$dbhost =   'localhost';
$dbuser =   'root'; 
$dbpwd  =   'xxx'; 
$dbname =   'experiments';
$db =   new mysqli( $dbhost, $dbuser, $dbpwd, $dbname );


$sql='insert into `stockmaster` ( `price`, `typeabbrev` ) values (?,?);';
$stmt = $db->prepare( $sql );

if( $stmt ){
    $stmt->bind_param('ss',$price,$abbrev);
    for( $i=0; $i < 100 ; $i++ ){
        shuffle( $r );
        shuffle( $a );
        $abbrev = $a[ array_rand( $a ) ];
        $price = $r[ array_rand( $r ) ];
        $stmt->execute();
    }
    $stmt->close();
}

A quick mysql cmd query

mysql> select * from stockmaster
+---------+-------+------------+
| stockid | price | typeabbrev |
+---------+-------+------------+
|       1 |  0.35 | TR         |
|       2 |  0.70 | FL         |
|       3 |  0.91 | FL         |
|       4 |  0.97 | DE         | 
...... etc ~ there are 100 rows 
|      99 |  0.78 | RT         |
|     100 |  0.81 | FL         |
+---------+-------+------------+
100 rows in set (0.00 sec)



mysql> select * from prices;
+-----+-------+------------+
| pid | price | typeabbrev |
+-----+-------+------------+
|  18 |  0.96 | TR         |
|  78 |  0.77 | FL         |
|  81 |  0.36 | RT         |
|  99 |  0.69 | DE         |
+-----+-------+------------+
4 rows in set (0.00 sec)


$sql="SELECT 
    sm.`stockid`, 
    ( SELECT `price` FROM `prices` WHERE `stockid` = sm.`stockid` AND `typeabbrev` = 'RT' ) AS 'RT', 
    ( SELECT `price` FROM `prices` WHERE `stockid` = sm.`stockid` AND `typeabbrev` = 'TR' ) AS 'TR', 
    ( SELECT `price` FROM `prices` WHERE `stockid` = sm.`stockid` AND `typeabbrev` = 'FL' ) AS 'FL', 
    ( SELECT `price` FROM `prices` WHERE `stockid` = sm.`stockid` AND `typeabbrev` = 'DE' ) AS 'DE' 
    FROM `stockmaster` sm";

$result = $db->query( $sql );
$rows = intval( $result->num_rows );

$result->close();
$db->close();

printf( "Rows: %d", $rows );

And this then outputs

Rows: 100

I realise the data in these test tables is not the same but I would be confident that with the actual data it would work in PHP...Weird!