Simple Query, Please Help!

Databases
21

Hmm, im guessing you thought i had used a type of set or enum. I did think of this, but in the end I thought it would just create more admin work than anything else. I want my script to work for itsself, with very minimal input from me. A simple admin page “add a download” is what i am eventually aiming for. Where most if not all of the actual content comes out of mysql.

At this point i take it that I should rather have something like modId1, modId2, modId3, etc… in my downloads table, with their default values set to null. Then use some kind of join to link them all together? Wow, what if a peice of software supports 100 devices?!

22

no, not like that

you need a separate table, and remove modId from the downloads table altogether

CREATE TABLE download_models
( downloadid INTEGER NOT NULL 
, modelid TINYINT NOT NULL 
, PRIMARY KEY ( downloadid, modelid )
);

suppose you had two downloads, frick and frack

INSERT INTO downloads ( catId, name, ... ) 
VALUES ( 9, 'frick', ... ) 
    , ( 37, 'frack', ... )
;

if frick comes in three models and frack in two, here’s how that might look –

INSERT INTO download_models VALUES
 ( 9 , 1 )
,( 9 , 2 )
,( 9 , 3 )
,( 37 , 1 )
,( 37 , 2 )
;
23

Ah, now you’re talking…

Well that does make perfect sense, it’s a no wonder you wrote a book on this subject! :slight_smile:

Im busy playing around with that idea now, I just have to figure out a query to display only the models relating to a certain downloads id.

Thank you SO much for your help!

24

Okay so I am stuck again. The concept I can grasp, but the query involved is mind boggling!

I have this so far:

SELECT downloads.name,models.model 
	FROM downloads,downloads_models 
	INNER 
		JOIN models 
		ON downloads_models.modId = models.model_id 
		WHERE downloads_models.downId = "'.$pageId.'"

To display the line that is supposed to echo the models that apply to this certain download, I am using this line of code:

$dpModSupport .= stripslashes($row['model']);

All this does is spit out every model value contained in downloads_models, eg:

model values: 1=8520,2=8900,3=9000,

If i have 1 = 1, 1 = 2, 1 = 3, 2 = 1, 3 = 3 in the downloads_models table,

It spits out this 85208900900085209000

Where am i going wrong here?

25

Write your code without the front end application. Test the code directly in the mysql client or a GUI like HeidiSQL or PHPMYADMIN (preferably the former).

Once your query is working correctly, anything after that is an error introduced in your front end code.

26

A simple yet useful bit of input, gives instant results too :slight_smile: Thank you.

I got it now, but okay, this might be more of a php question, but i am not really sure.

It now displays as i wanted it to, but I am left with a new problem. It will display my results as “85208900”. Which are the models that match the download id. But how do i get a space, or a comma inbetween there?

27

Got it! (was more php)

Had to make use of implode.