MySQL query

[donoe]

Hi, i've a problem with a mysql query but i'm not sure if it mysql can do it, or i can only handle it with php.

i've a table that looks like this

---

id | s1 | s2 | s3 | s4 | s5 |

---

7 | NULL| 1 | NULL| NULL | 1 |

---

i need to do a search, that only returns fields s2 and s5, because i do not need fields that have NULL values in them. should this be done in sql query, or i should rely on php to parse e results for me? (i've got many s* fields, so this may take a while)

a short ques: if i want to search two tables, can i do this? select * from tbl1,tbl2 where tbl1field=1,tbl2field=2 ?

would appreciate any help! thank you!

[stutterbug]

Unless you know ahead of time what records will be null (which I am guessing is unlikely), you can't send the database a cusom query asking for only the populated fields. It will send you everything you ask for, regardless of a field's contents. Instead, you will have to loop through the fields in your script, extracting the data you need and dropping the nulls. That is if you want to keep this table structure, though.

It is possible that your database structure is what is hampering you, though. If S1, S2,... Sn are all the same sort of data (let us say, 'colors_you_like', or 'recipe_ingredients'), then I would break this into two tables or at least collapse the table into an id and one other column. When you run a query against it, you won't get one single record with multiple fields. You will be left with multiple records with only populated values.

Hope that helps.

[donoe]

thank you for your prompt reply bug.

yes you are correct, i do not know before hand which are the fields that i'm expecting to be null.

and yes the structure of the table is hampering me. but i could not think of a better way to store the data.

for e.g,
i have userid1 likes orange, green and red
while userid2 likes orange blue and purple

i cant think of a way to store their preferences individually, while being able to do a select on only orange.

if my table has 2 fields, 1 userid, 1 'colours i like', then how can i do a select search to further peep inside each value, and see if 'orange' is inside a list of colors?

hope you can help, thank you 🙂

[stutterbug]

I'm afraid you are going to have to modify your table definitions. Take this example as a starting point:

Table "users"
+---+-----------+
|id | name      |
+---+-----------+
| 1 | John　　　|
| 2 | Paul　　　|
| 3 | George  　　|
| 4 | Ringo　　 　|
+---+-----------+

Table "preferences"
+---+-------+----------+
|id |user_id|pref      |
+---+-------+----------+
|1  |1      |orange    |
|2  |1      |red       |
|3  |2      |blue      |
|4  |2      |red       |
|5  |2      |green     |
|6  |2      |eggshell  |
|7  |3      |blue      |
|8  |3      |red       |
|9  |3      |eggshell  |
|10 |3      |puce      |
|11 |4      |postoffice|
+---+-------+----------+

# To find out what members like 'orange':
SELECT name FROM users INNER JOIN preferences ON users.id = preferences.user_id WHERE pref = "orange"

It's not the easiest thing in the world, but you can find out virtually everything you could possibly want to know about the relationship between 'users' and 'preferences' with a single query. If it is too tough, do not hesitate to break it into two or more queries.

[donoe]

thank you to you bug! now i see what you mean, and yes its indeed a much more efficient method.

not a hassle to change the table definitions as long as its more efficient!

thanks again =p

[donoe]

sorry bug, and experts,

i have another problem again..

ive changed the table structure to that suggested by bug ("preferences")

any idea what's e command to return only the first instance of userid, even though there are many of them?

reason being if say i want to do on a particular userid for a
while(row=fetch_assoc) {
output stuffs
}

then they will churn out duplicate entries, how can i just choose the first instance of the userid, and stop there?

[stutterbug]

You need to use 'GROUP BY column_id'

SELECT * FROM table WHERE pref = 'blue' OR pref = 'red' GROUP BY user_id

That should give you what you want.

[donoe]

very much thank you bug!