I am able to get both the value and row of the mysql query result.
But I am struggling to get the single output of a query. e.g.:
$result = mysql_query("SELECT COUNT(*) FROM Students;");
I need the result to display. But I am not getting the result.
I have tried with the following methods:
mysql_fetch_assoc()mysql_free_result()mysql_fetch_row()But I didn't succeed to display (get) the actual value.
The answer to which one is better is "it depends." As with anything, there are a variety of different approaches and you should also keep in mind that code that uses objects is not necessarily object oriented but can still be written procedurally. In the same vein, code that does not use objects can still be modular.
I would choose to use the mysqli class every time, though. There is no significant difference in performance. You probably won't realize some of the advantages of using a DB class such as simplified polymorphism, so my only argument for using the class is that I prefer the syntax. However, rather than use mysqli directly I would probably recommend that you extend or compose it. You can only do this with the class.
class DB extends mysqli {
public function __construct() {
parent::__construct($_SERVER['DB_HOST'],
$_SERVER['DB_USER'], $_SERVER['DB_PASS']);
}
}
This is a very shallow example.
An example of the polymorphism I was talking about above would be something like this:
class User implements DAO {
private $db;
public function __construct(DB $db) {
$this->db = $db;
}
}
//Testing code is simplified compared to using it in production
class TestDB extends DB {}
new User(new TestDB);
new User(new DB);
By the way I categorically prefer PDO over mysqli
Your problem is, your query isn't returning what you think it returns (it always helps to run you query standalone, to see if the result set is what you expect).
Right, let's break this down.
It is counting all posts that the user has not liked or disliked. likes and dislikes are stored in the taxi table. taxi.taxiID matches post.ID. Hence if the userID with any value that isn't null is found, ignore that post.ID. I am counting those post.ID which are not ignored
You're trying count all posts that don't have a matching record in the taxi table, for that userID. What you want here is to JOIN the tables and get those rows in post that would normally be excluded by the join. This is achieved by an left outer join
(edited)
SELECT p.ID, t.taxiID
FROM post p LEFT OUTER JOIN taxi t ON p.ID = t.taxiID AND t.userID = '.$user.'
HAVING t.taxiID IS NULL
That HAVING clause is saying: only those rows in the resultset that didn't have a corresponding t.taxiID.
If you run this query and get the expected set of rows (posts that do not have likes or dislikes by that user) THEN you can add an outer query to count the number of returned rows:
SELECT COUNT(*) as count FROM (
SELECT p.ID, t.taxiID
FROM post p LEFT OUTER JOIN taxi t ON p.ID = t.taxiID AND t.userID = '.$user.'
HAVING t.taxiID IS NULL
) a
This should return a single scalar named count. In this case you'll be able to say:
if ($count[0]->count > 10) { blah blah blah }
(2nd edit) This inner query will get you those posts that have either value = 1 in the taxi table, or no value at all, which results in 4 being returned for your example:
SELECT p.ID, t.taxiID, t.value
FROM post p LEFT OUTER JOIN taxi t ON p.ID = t.taxiID AND t.userID = '.$user.'
HAVING t.taxiID IS NULL OR t.value = 1
from http://www.php.net/manual/en/function.mysql-fetch-assoc.php:
Returns an associative array of strings that corresponds to the fetched row, or FALSE if there are no more rows.
(emphasis mine) It's probably just easier for the MySQL driver to give you everything as a string, and assume you will know what to do with it. Just use intval() to get the integer value.
It was really really awesome http://www.phpsimplicity.com/tips.php?id=1
it is so simple! no need to work with huge classes! I'm Happy:D
<!DOCTYPE html>
<html lang="en">
<head>
<title>Paginate</title>
</head>
<body>
<form method='get'>
<?php
$connection = Mysql_connect( 'server', 'user', 'pass' );
if ( ! $connection ) {
echo 'connection is invalid';
} else {
Mysql_select_db( 'DB', $connection );
}
//Check if the starting row variable was passed in the URL or not
if ( ! isset( $_GET['startrow'] ) or ! is_numeric( $_GET['startrow'] ) ) {
//We give the value of the starting row to 0 because nothing was found in URL
$startrow = 0;
//Otherwise we take the value from the URL
} else {
$startrow = (int) $_GET['startrow'];
}
//This part goes after the checking of the $_GET var
$fetch = mysql_query( "SELECT * FROM sample LIMIT $startrow, 10" ) or
die( mysql_error() );
$num = Mysql_num_rows( $fetch );
if ( $num > 0 ) {
echo '
<table border=2>';
echo '
<tr>
<td>ID</td>
<td>Drug</td>
<td>quantity</td>
</tr>
';
for ( $i = 0; $i < $num; $i ++ ) {
$row = mysql_fetch_row( $fetch );
echo '
<tr>';
echo "
<td>$row[0]</td>
";
echo "
<td>$row[1]</td>
";
echo "
<td>$row[2]</td>
";
echo '
</tr>
';
}//for
echo '
</table>
';
}
//Now this is the link..
echo '<a href="' . $_SERVER['PHP_SELF'] . '?startrow=' . ( $startrow + 10 ) . '">Next</a>';
$prev = $startrow - 10;
//only print a "Previous" link if a "Next" was clicked
if ( $prev >= 0 ) {
echo '<a href="' . $_SERVER['PHP_SELF'] . '?startrow=' . $prev . '">Previous</a>';
}
?>
</form>
</body>
</html>
By the way link of rickyduck was good too
You need to alias the aggregate using the
askeyword in order to call it frommysql_fetch_assoc