where clause not working

[dodgeitorelse]

I have this query to get total number of rows that meet conditions. The result should be 2 but I am only getting one. Can the where clause be used for multiple columns with the where clause?

 

$sql_query = 'SELECT * FROM song_info WHERE videosband <> "" and videosband is not null and videosguitar <> "" and videosguitar is not null';

//store the SQL query in the result variable

$result = mysql_query($sql_query);

$rows = mysql_num_rows($result);

//output total

echo "<font style='chiller' color='lime' size=3>$rows</font>";

 

[DavidAM]

Are you sure there are two records in your database that have both of these columns not blank?  I don't see a problem with the query at all.  Or were you trying to get a count where one of the columns is not blank?

SELECT * FROM song_info 
WHERE (videosband <> "" and videosband is not null)
   OR  (videosguitar <> "" and videosguitar is not null)

 

 

By the way, if you are not using the data from that query, you are wasting a lot of resources to get a count. 

$sql_query = 'SELECT COUNT(*) FROM song_info 
WHERE videosband <> "" and videosband is not null 
and videosguitar <> "" and videosguitar is not null';
//store the SQL query in the result variable
$result = mysql_query($sql_query);
$data = mysql_fetch_row($sql_query);
$rows = $data[0];
//output total
echo "<font style='chiller' color='lime' size=3>$rows</font>";

 

 

[dodgeitorelse]

I am trying to show a total number of rows that have data for the 2 fields called videosband and videosguitar. Each field has one data in each. So my result is 1 but should be 2. I don't want the empty or null rows included in the total . I am also a noob with php.

[dodgeitorelse]

ok I have put one set of data into videosband column and 5 sets of data into videosguitar column.

when I run this script  ...

 

$sql_query = 'SELECT * FROM song_info

WHERE videosband <> "" and videosband is not null and videosguitar <> "" and videosguitar is not null';

//store the SQL query in the result variable

$result = mysql_query($sql_query);

$rows = mysql_num_rows($result);

//output total

echo "<font style='chiller' color='lime' size=3>$rows</font>";

 

I get a return of 1

 

when I run  ....

 

$sql_query = 'SELECT * FROM song_info

WHERE videosband <> "" and videosband is not null';

//store the SQL query in the result variable

$result = mysql_query($sql_query);

$rows = mysql_num_rows($result);

//output total

echo "<font style='chiller' color='lime' size=3>$rows</font>";

 

I get a result of 1.

 

when I run ....

 

$sql_query = 'SELECT * FROM song_info

WHERE videosguitars <> "" and videosguitars is not null';

//store the SQL query in the result variable

$result = mysql_query($sql_query);

$rows = mysql_num_rows($result);

//output total

echo "<font style='chiller' color='lime' size=3>$rows</font>";

 

I get a result of 5.

 

This tells me that I am reading data for each individual query but when I try both where conditions together I am only reading 1 of the 2 conditions. I also tried to change ....

 

WHERE videosband <> "" and videosband is not null';

 

to ....

 

WHERE videosband <> "" or videosband is not null';

 

Using and gives me the result of 1, using or gives me the result of 5.

 

So I am lost as to how to get code to read data from both columns and add the 2 totals together to give me a result of 6.

 

I also tried code the way you suggested and got blank results meaning no errors and no numbers displayed.

 

Please help me.

 

 

 

[DavidAM]

Put your code in code tags (it's the # button for code and the one next to it

 for PHP). It makes it a lot easier to read.

Let's take a look at the three queries you posted. Starting at the bottom:
[code]SELECT * FROM song_info
WHERE videosband <> "" and videosband is not null

Is asking for all rows where the "videosband" column is not empty (an empty string or NULL).

 

SELECT * FROM song_info
WHERE videosguitars <> "" and videosguitars is not null

Is asking for all rows where the "videosguitars" column is not empty.

 

SELECT * FROM song_info
WHERE videosband <> "" and videosband is not null 
and videosguitar <> "" and videosguitar is not null

Is asking for all rows where the "videosband" and the "videosguitar" columns are both not empty.

 

From the results you posted (1, 5, and 1) it appears that you have 1 row where the videosband column is not empty; and you have 5 rows where the videosguitar column is not empty.

 

The question now becomes, what is it you are trying to find out with this query? It appears that you have only 1 row with complete data (both columns filled in). You have (apparently) 5 rows with one or the other (or both) columns complete. That does not necessarily mean there are 6 rows, there could be only 5.

 

If you want to know how many rows have data in BOTH of the two columns, you should use AND:

SELECT * FROM song_info
WHERE IFNULL(videosband, "") <> "" 
and IFNULL(videosguitar, "") <> "" 

[Note the IFNULL expression which says IF videosband IS NULL, consider it to be "" (an empty string). This is the same condition you wrote, it just simplifies things and is probably more efficient.]

 

If you want to know how many rows have data in one OR the other column, you should use OR:

SELECT * FROM song_info
WHERE IFNULL(videosband, "") <> "" 
OR IFNULL(videosguitar, "") <> "" 

 

If you want to keep with your original condition style and use the OR, you have to group the two conditions for each column together as in my first post:

SELECT * FROM song_info
WHERE (videosband <> "" and videosband is not null)
   OR  (videosguitar <> "" and videosguitar is not null)

Note the parenthesis around the pair of videosband conditions and again around the videosguitar conditions.

 

If you absolutely need the count of 6 (in this case) you would have to run both queries, which you can do in a UNION:

SELECT * FROM song_info WHERE IFNULL(videosband, "") <> "" 
UNION ALL
SELECT * FROM song_info WHERE IFNULL(videosguitar, "") <> "" 

Which should return all six rows (one of them will be in there twice). If you don't use the ALL phrase on the UNION, the server will filter out the duplicate row (I think).

[dodgeitorelse]

yes that is exactly what I need. the Union All did the trick. ty very much. I actually have 5 colums for the videos and each row has a different video for each column. Currently there could be 771 rows, so if I have a video for every row at each column it will show a total of 3855 videos available. Wow ty so much.