how do i streamline this code

[An7hony]

Hi Guys

 

how do i streamline this code and generally make it better?

 

<?php

$query2 = "SELECT id, col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10 FROM prices WHERE product = '".$product_name."' AND col_1 !='' or col_2 !='' or col_3 !='' or col_4 !='' or col_5 !='' or col_6 !='' or col_7 !='' or col_8 !='' or col_9 !='' or col_10 !=''  ORDER BY id";

$res2 =mysql_query($query2) or die(mysql_error());

$num_rows = mysql_num_rows($res2);

$i=1;
while($row = mysql_fetch_array($res2)){

while($i<=$num_rows)
  {
  echo "<li><a rel=\"nofollow\" class=\"opt\" href=\"#\">".$row[$i]."<span>".$row[$i]."</span></a></li>";
  $i++;
  }
}
?> 

[dzelenika]

Firstly there is an logical error in Your query. all "OR"cols should be grouped in parenthesis.

 

SELECT id, col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10

FROM prices

WHERE product = '".$product_name."' AND (col_1 !='' or col_2 !='' or col_3 !='' or col_4 !='' or col_5 !='' or col_6 !='' or col_7 !='' or col_8 !='' or col_9 !='' or col_10 !='' )

ORDER BY id

 

You should change SELECT id, col_1, col_2, col_3, col_4, col_5, col_6, col_7, col_8, col_9, col_10 to SELECT *

 

[An7hony]

without the brackets it pulls the correct result. With the brakets it doesnt pull the right number.

 

I was just hoping there was a way to write this where i didn't have to write out the col_1 !='' or col_2 !='' or col_3 !='' or col_4 !='' or col_5 !='' or col_6 !='' or col_7.

Was thinking of a loop col_$i !=''

And a way of finding out how many cells was in the table i.e col_1 to col_10?

[trq]

Having database columns such as col_1, col_2, col_3 etc etc wreaks of poor design. I would start there. Google some tutorials on "Database Normalisation" because as it stands your database design is terrible and your code won't make it any better.

[An7hony]

i'll take a look thanks.

 

 

[ManiacDan]

I putting the (correct) brackets into your (wrong) query produces what you call the "wrong" results, you might not be understanding what it's trying to do.  Right now, your query will return rows if:

product = '".$product_name."' AND col_1 !=''

OR

col2 != ''

OR 

col3 != ''

etc.

That doesn't appear to be what you want.

 

Also, Thorpe is absolutely right about your design.  Either:

A)  These fields all should be named properly and you just didn't bother for some reason.  You should name the fields in this case.

B)  These fields all hold the same kind of data and you're adding a new numbered column whenever you add more data.  You should break these columns out to another table in this case (which would have the side effect of making your query easy)