MySQL select where column LIKE and is not empty

Question

I have the following query and I'm trying to select only the rows where I have picture.

In table kat_gl_picture I have 3 categories, but I don't have picture in all 3 categories yet!

All work just fine, but I have printed name of third category, where I don't have picture.

I tried WHERE link LIKE '%$first_var%'AND NOT (link <=> NULL) ....IS NOT NULL - but nothing yet worked. Tabele1 and 2 and web problem solved

 <?
    include("connection.php");
    $kategorije = mysql_query("SELECT * FROM kat_gl_picture ORDER BY rbr");
        while ($red=mysql_fetch_array($kategorije))                 
        {
        $first_var = $red['kat'];                   
        $result = mysql_query("SELECT id, naziv, ime, tekst, username, link, file_name, datum FROM Tab_Pic_Pic
    WHERE link LIKE  '%$first_var%'
                   ORDER BY id");
        echo '<table>';
    echo '<tbody>';
        echo $first_var;    
        echo '<tr>';
                   echo '<TD valign="top">';
        while ($row=mysql_fetch_array($result))
        {
        list($x, $y) = getimagesize("admin /upload/".$row['file_name']);
        if ($x>$y)  {
    $y=($y/$x)*150;
    $x=150;
    }
    else
    {
    $x=($x/$y)*115;
    $y=115;
    }
    $ID_broj = $row["id"];
        $tekst_broj = $row["tekst"];
    ?>
    <?     echo '<img src="admin /upload/'.$row['file_name'].'" height="'.$y.'" width="'.$x.'"/>';?>            
    <? 
    }
    echo '</td>';
    echo '</tr>';
    echo '</tbody>';
    echo '</table>';
    }

You don't need to check if link is null, if link is LIKE something other than null then it can't be null... Keep in mind though, null is not the same as an empty string. — Devon Bessemer, Dec 14 '16 at 18:01
select the records from the database where profiles is not empty then do the functionality (better to select the records which is not null) — Soniya Basireddy, Dec 14 '16 at 18:05
***Please [stop using `mysql_*` functions](http://stackoverflow.com/questions/12859942/why-shouldnt-i-use-mysql-functions-in-php).*** [These extensions](http://php.net/manual/en/migration70.removed-exts-sapis.php) have been removed in PHP 7. Learn about [prepared](http://en.wikipedia.org/wiki/Prepared_statement) statements for [PDO](http://php.net/manual/en/pdo.prepared-statements.php) and [MySQLi](http://php.net/manual/en/mysqli.quickstart.prepared-statements.php) and consider using PDO, [it's really pretty easy](http://jayblanchard.net/demystifying_php_pdo.html). — Jay Blanchard, Dec 14 '16 at 18:23
[Little Bobby](http://bobby-tables.com/) says ***[your script is at risk for SQL Injection Attacks.](http://stackoverflow.com/questions/60174/how-can-i-prevent-sql-injection-in-php)***. Even [escaping the string](http://stackoverflow.com/questions/5741187/sql-injection-that-gets-around-mysql-real-escape-string) is not safe! — Jay Blanchard, Dec 14 '16 at 18:23

Jesse Ivy · Accepted Answer · 2016-12-14T23:10:01.363

0

The problem is that I cannot determine if there is a foreign key in your Tab_Pic_Pic table. You can however join on a LIKE.

Try this:

SELECT * FROM Tab_Pic_Pic JOIN kat_gl_picture ON Tab_Pic_Pic.link LIKE CONCAT('%', kat_gl_picture.kat, '%') ORDER BY kat_gl_picture.kat, Tab_Pic_Pic, rbr

It should give you a list of all pictures including the kat_gl_picture.kat field which you can just use a local variable to detect change.

Would be easier to provide a more accurate example if I had the full table info. Standard method is to have a foreign key in Tab_Pic_Pic that references the corresponding primary key from the kat_gl_picture table.

Alternatively, if you simply want to omit the blank category it can be done in your PHP code like this:

if(mysqli_num_rows($result)>0){
    echo '<table>';
    echo '<tbody>';
    echo $first_var;    
    echo '<tr>';
               echo '<TD valign="top">';
    while ($row=mysql_fetch_array($result))
    {
    list($x, $y) = getimagesize("admin /upload/".$row['file_name']);
    if ($x>$y)  {
    $y=($y/$x)*150;
    $x=150;
    }
    else
    {
    $x=($x/$y)*115;
    $y=115;
    }
    $ID_broj = $row["id"];
    $tekst_broj = $row["tekst"];
    ?>
    <?     echo '<img src="admin /upload/'.$row['file_name'].'" height="'.$y.'" width="'.$x.'"/>';?>            
    <? 
    }
    echo '</td>';
    echo '</tr>';
    echo '</tbody>';
    echo '</table>';
}

Wrapping the output of your HTML in a simple condition like this should illustrate that the script itself is inefficient. Trust me when I say it can all be accomplished with one query. Do not get frustrated. You'll figure it out. Programming is hard.

edited Dec 14 '16 at 23:10

answered Dec 14 '16 at 18:26

Jesse Ivy

335
1
10

I don't see `group by` in the script – bassxzero Dec 14 '16 at 18:28
"GROUP BY kat_gl_picture.kat" exists in my suggested modification to the first SQL statement he is using to pull the picture categories. I'm suggesting that all pictures can be pulled with one query. Don't understand why he using a nested loop to iterate the categories but based on the naming conventions it's tough to tell. – Jesse Ivy Dec 14 '16 at 18:32
If my assumptions are correct, the following query will return all pictures and make the second query unecessary.SELECT * FROM kat_gl_picture LEFT JOIN Tab_Pic_Pic ON kat_gl_picture.kat=Tab_Pic_Pic.link where Tab_Pic_Pic.link IS NOT NULL ORDER BY rbr – Jesse Ivy Dec 14 '16 at 18:37
Hi guys, thanks for answering, I will try to implement your suggestion, but I doubt to understand what you mean. Look picture, please, maybe tey will help to understand my problem and help to solv this. Thanks – Gerov Boban Dec 14 '16 at 22:21
I've changed my answer fairly dramatically, adding the LIKE to the JOIN clause. Please review. – Jesse Ivy Dec 14 '16 at 23:33
Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, resource given in /home/pbsorgrs/public_html/galerija_1.php on line 546 – Gerov Boban Dec 15 '16 at 00:29
I'm frustrated, that is correct, because my English is bad... all my life i'm learning Russian language... :) I putt mysqli_num_rows > 0, and get this message...... Warning: mysqli_num_rows() expects parameter 1 to be mysqli_result, resource given in /home/pbsorgrs/public_html/galerija_1.php on line 546 ..... I still have free this messages, so i think i cannot get rid of third category. I put new picture, take a look if you have a time. Thanks for giving mi support, man. – Gerov Boban Dec 15 '16 at 00:39
Strange result. The warning implies that there is an error in your SQL query. I really recommend combining the two queries with a join like I suggested but I understand if that seems too complicated. Would be good experience. It will return both table's data in each row. To resolve the warning you'll need to determine what the $result variable is in that context as it is apparently not a result set. Check the error log. Try outputting the value with print_r($results). You're English is comprehensible and probably better than my French. – Jesse Ivy Dec 15 '16 at 18:14
Just keep trying new things. When you hit a wall, google any related info you have (for example, the warning text). When you google the warning text you can see that it is a type problem. $results is probably a Boolean value or some other data type. You're welcome. Glad to help. – Jesse Ivy Dec 15 '16 at 18:17
Problems at school, I am long gone. Sorry about that. I solved the problem by adding colone in the table. I have still a lot to learn. The problem is my spare time. Thank you for your answers and your time. I sent a picture which shows that now seems gallery. Now I have a new problem ... – Gerov Boban Dec 30 '16 at 21:24

MySQL select where column LIKE and is not empty

1 Answers1