0

Following is my sql query:

$result = mysqli_query($con,"SELECT url,img_url,sentiment,title,category from invite_page where category='".$category."' where today = '".$today."' order by rt_count desc limit 3 ");
while ($row = @mysqli_fetch_array($result))
{
    $url = $row['url'];
    $img_url = $row['img_url'];
    ...
}

I am displaying this fetched values to user in some display box.

  1. Problem is in few rows title and img_url fields are empty in database. I want to fetch only those record where all fields are filled. How to to this?

  2. If there are not 3 records for today = '".$today."' then it should take records in desc order of today. (desc order of rt_count should also persist. I want to show record starting from max value to lower)

How to do this?

Improve this question

2 Answers 2

Reset to default
2
  1. Add checks of those fields to the WHERE clause.
  2. Change the today check from = to <=, and add it to the ORDER BY clause.


SELECT url,img_url,sentiment,title,category 
FROM invite_page
WHERE category='".$category."' 
  AND today <= '".$today."' 
  AND title != ''
  AND img_url != ''
ORDER BY today DESC, rt_count DESC
LIMIT 3
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Oh yes, if he wants this than its way cleaner than mine +1 and anyways the question is damn confusing so got rid of my answer
It would be better if you used mysqli_prepare and parametrized queries. If you use mysqli_query, you should call mysqli_real_escape_string on any user-supplied inputs.
2

You should stop using the database extension just like the old, long deprecated mysql extension. This is unsafe, since it allows sql injections. Use the newer mysqli extension or pdo combined with prepared statements which solved those problems. Start with this query 'prototype':

SELECT url,img_url,sentiment,title,category 
  FROM invite_page 
  WHERE category = :category AND today <= :today
    AND title IS NOT NULL AND img_url IS NOT NULL
  ORDER BY rt_count desc, rt_count desc 
  LIMIT 3

Whether you have to check like title IS NOT NULL as in the example above or title!='' depends on your database setup, if those columns are NULL or hold an empty string as content.

Next 'bind' your parameters to the statement before execution (the exact syntax depends on the extension you use, there are millions of examples out there on google for this: 

$db->bind('category', $category);
$db->bind('today', $today);

Next, execute the prepared statement.

Note, that this does not work out of the box, it is meant to point you into the right direction. You definitely have read some documentation and study some examples you find on the internet.

Improve this answer

4 Comments

He'snot using the deprecated mysql extension, he's using mysqli.
Indeed, but he uses mysqli just like the old mysql extension. This results in exactly the same problem, so more or less the same advises apply. Thanks for pointing this out!
What does the old extension getting removed from PHP (which is NEVER actually going to happen, there are just too many sites using it) have to do with him? You agreed that he's not using the old extension. Using the new extension in the same way as the old extension doesn't make him dependent on the old extension.
@Barmar You are right that he does not depend on the old extension, thanks again for pointing that out. Whether the old extension will be removed in future or not is something no one can say, especially since this depends on who actually builds php for what purpose. Generally the intention of deprecating something is to be able to remove it in future. So there is a potential danger.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.