CODEWITHSUNDEEP
javascriptphpmysql
atomsmasher
atomsmasher

Reputation: 745

limit php query search of mysql database to non empty rows

I have a php file that connects to a mysql database and looks for some data and writes to a file:

<?php
$dbc = mysql_connect('localhost', 'user', 'passwrd');
mysql_select_db('database', $dbc);


$result = mysql_query("SELECT responses FROM ttable");
$to_encode = array();

while($row = mysql_fetch_assoc($result)) {
    $to_encode[] = $row;
}

$file = 'par.txt';
file_put_contents($file, json_encode($to_encode));

?>

this file is executed with the following javascript function:

var get_database_id = {
function () {
    $.getJSON('query.php', function(data) {
    $.each(data, function(fieldName) {
        $("#" + fieldName);
    });
});
return 'done'; 
}
};

so far everything works but the file that is written, 'par.txt' contains all rows of the column 'responses' in the table of the database, while I only want rows where there is data. this is what par.txt looks like:

[{"responses":"{\"Q0\":\"2\",\"Q1\":\"sf\",\"Q2\":\"4\"}"},{"responses":""}{"responses":""},{"responses":""},{"responses":""},{"responses":""},

In the end it's a huge file, I want to cut back on the size since I read the file and search it for the responses. In the example above i would only want to select the row with the data {"responses":"{\"Q0\":\"2\",\"Q1\":\"sf\",\"Q2\":\"4\"} and skip everything else until I come across another row with data. How do I do this?

Upvotes: 0

Views: 35

Answers (1)

Lelio Faieta
Lelio Faieta

Reputation: 6689

Use a WHERE condition to say that responses is not empty.

So your query will become:

SELECT responses FROM ttable WHERE responses <> ''

Also consider to switch to mysqli_* API or better PDO since mysql_* is a deprecated API removed in the php 7 version.

Also learn about prepared statements to avoid SQL INJECTIONS

Upvotes: 2

Related Questions