mysql call statement with mysqli

[SimonIoa]

Hello, i have a problem with a variable i use to call a statement. The statement its ok, returns data from database as wanted, i checked it on phpmyadmin. I think that the problem is on the variable. Here is the code

   $select =mysqli_query($db,"SELECT * FROM table WHERE row is 'something'");

   $Order = " ORDER BY table.row DESC";

   $Filter = " table.row = " .  $_GET['url'];

   if($Filter != "") $select .=  " AND " . $Filter;
        $query = mysqli_query($select . $Order);

        $perRow = 2; 
        $counter = 0; 
        if (mysqli_num_rows($query) > 0 )
        {
            echo '<tr>'; 
            while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))
            {

the statements i use they are ok i checked them on phpmyadmin mysql

[cereal]

Hi,

since you set $Filter on line 5 the condition on line 7 will never verify, so you could remove it. What you should do is to verify if the GET attribute is set and valid, do:

$url = filter_input(INPUT_GET, 'url', FILTER_VALIDATE_URL);

Then replace the IF condition at line 7 with:

if($url !== FALSE)

Docs:

• http://php.net/manual/en/function.filter-input.php
• http://php.net/manual/en/filter.filters.validate.php

[SimonIoa]

ok how i call the table and the row from the database?
for example these are all the filters i use

$select =mysqli_query($db,"SELECT created, views_count, message, category_id from table AS M ");
        if (isset($_GET['recent']) && ($_GET['recent'] == 'true'))
        {
            $Order = " ORDER BY M.created DESC";
        }
        else if (isset($_GET['most']) && ($_GET['most'] == 'true'))
        {
            $Order = " ORDER BY M.views_count DESC";
        }
        elseif (isset($_GET['audiocountry']) && $_GET['audiocountry'] != '')
        {

            $Filter = " M.country_id = " .  $_GET['audiocountry'];
        }
        elseif (isset($_GET['audiocategory']) && $_GET['audiocategory'] != '')
        {
            $Filter = " M.category_id = " .  $_GET['audiocategory'];
        }
        elseif (isset($_POST['search_audios']))
        {
            if (empty($_REQUEST['src_audio']) || $_REQUEST['src_audio'] == 'Search for Audios By Name')
            {
                echo $lang['YouForgotToEnterSearchTerm'];
                exit;
            }
            elseif (isset($_POST['src_audio']))
            {
            $Filter = " M.message LIKE '%" . $_REQUEST['src_audio'] .  "%'";
            $Order = " ORDER BY M.created DESC";
            exit;
            }
            elseif ($Filter = " M.message NOT LIKE '%" . $_REQUEST['src_audio'] .  "%'")
            {
                echo "Sorry, but we can not find an entry to match your query<br><br>";
            }
        }
        else
        {
            $Order = " ORDER BY M.message DESC";
        }
        if($Filter != "") $select .=  " AND " . $Filter;
        $query = mysqli_query($select . $Order);

        $perRow = 2; 
        $counter = 0; 
        if (mysqli_num_rows($query) > 0 )
        {
            echo '<tr>'; 
            while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))

[SimonIoa]

this is what i did but the data wont return back- not shown. If i am getting this right i replace the GET in a variable($url) and then call it
like this.

elseif (isset($_GET['audiocountry']) && $_GET['audiocountry'] != '')
{
    $url = filter_input(INPUT_GET, 'audiocountry', FILTER_VALIDATE_URL);
    $Filter = " M.country_id = " .  $_GET['audiocountry'];
}
 if($url !== FALSE) $select .=  " AND " . $Filter;

[cereal]

At line 1 instead of:

$select =mysqli_query($db,"SELECT created, views_count, message, category_id from table AS M ");

write:

$select = "SELECT created, views_count, message, category_id from table AS M ";

So at line 42 you can execute it correctly.

[SimonIoa]

still the same.. i user this

$select = "SELECT * FROM table as M ";
if (isset($_GET['recent']) && ($_GET['recent'] == 'true'))
{
$Order = " ORDER BY M.created DESC";
}
else if (isset($_GET['most']) && ($_GET['most'] == 'true'))
{
$Order = " ORDER BY M.views_count DESC";
}
elseif (isset($_GET['audiocountry']) && $_GET['audiocountry'] != '')
{
$url = filter_input(INPUT_GET, 'audiocountry', FILTER_VALIDATE_URL);
$Filter = " M.country_id = " .  $_GET['audiocountry'];
}
elseif (isset($_GET['audiocategory']) && $_GET['audiocategory'] != '')
{
$url = filter_input(INPUT_GET, 'audiocategory', FILTER_VALIDATE_URL);
$Filter = " M.category_id = " .  $_GET['audiocategory'];
}
elseif (isset($_POST['search_audios']))
{
if (empty($_REQUEST['src_audio']) || $_REQUEST['src_audio'] == 'Search for Audios By Name')
{
echo $lang['YouForgotToEnterSearchTerm'];
exit;
}
elseif (isset($_POST['src_audio']))
{
$url = filter_input(INPUT_REQUEST, 'src_audio', FILTER_VALIDATE_URL);
$Filter = " M.message LIKE '%" . $_REQUEST['src_audio'] .  "%'";
$Order = " ORDER BY M.created DESC";
exit;
}
elseif ($Filter = " M.message NOT LIKE '%" . $_REQUEST['src_audio'] .  "%'")
{

$url = filter_input(INPUT_REQUEST, 'src_audio', FILTER_VALIDATE_URL);
echo "Sorry, but we can not find an entry to match your query<br><br>";
}
}
else
{
$Order = " ORDER BY M.message DESC";
}
if($url !== FALSE) $select .=  " AND " . $Filter;
$query = mysqli_query($select . $Order);

$perRow = 2; 
$counter = 0; 
if (mysqli_num_rows($query) > 0 )
{
echo '<tr>'; 
while($row=mysqli_fetch_array($query,MYSQLI_ASSOC))

[cereal]

Ok, a part those exit; at lines 25 and 32 I don't see anything wrong, those will stop the execution of the script, in one of the cases it setting $Filter and $Order and then stopping, the query will not be executed.

Also, you wrote you have tested the query on PHPMyAdmin and that works, I'm in doubt if you have tested the one generated by the script or the intended query. Could you paste what was generated? In practice instead of:

$query = mysqli_query($select . $Order);

do:

$query = $select . $Order;
echo $query;

And return it or simply test it. You could also add error checking to see if the database returns an error code.

[SimonIoa]

ok the echo returns this

SELECT M.msg_id, M.uploads, M.country_id, M.message, M.description, M.type, M.text, M.views_count, U.username, C.name as name, CA.name as category_name FROM users AS U LEFT JOIN messages AS M ON U.uid = M.uid_fk LEFT JOIN countries AS C ON M.country_id=C.country_id LEFT JOIN categoriestol AS CA ON M.category_id=CA.c_id WHERE M.type='A' AND ORDER BY M.message DESC  

SO IT ADDS THE 'AND' so i think thats the problem.

[SimonIoa]

i changed to what i used to use. Now the AND doesnt appear

SELECT M.msg_id, M.uploads, M.country_id, M.message, M.description, M.type, M.text, M.views_count, U.username, C.name as name, CA.name as category_name FROM users AS U LEFT JOIN messages AS M ON U.uid = M.uid_fk LEFT JOIN countries AS C ON M.country_id=C.country_id LEFT JOIN categoriestol AS CA ON M.category_id=CA.c_id WHERE M.type='A' ORDER BY M.message DESC

but it still doesnt work-doesnt returns the data. Although it returns ok on phpmyAdmin

[cereal]

Don't know your current code but, you're using procedural style, so the first argument for mysqli_query() is the connection link, which is missing from your last pasted code. It was set in the first line of your original script but not at line 42. I forgot to point that particular. Try to add that and it should work fine:

$query = mysqli_query($db, $select . $Order);

[SimonIoa]

no still doesnt work... it should but no