Simple Query, Please Help!

Databases
1

Hi,

I am trying to create a means to dynamically display some links to other websites on my own website.

I have basically created 2 tables.

One is called domains it is made up of two fields, an auto incremented id
and the domain name, so basically it would read as: “1 example.com”

The other table is called links’ it is made up of a few fields, its own auto incremented id, a field called domainId, a field that holds a link to the page on the domain, and a little description about the link.

What I am trying to do is this:

$loopResult.= '
	   <ul class="bymodel-dllinks">
	    <li><a href="'.$linkAd.'" target="_tab">'.$linkDesc.' '.$linkDomain.'</a></li>
	   </ul>
	 ';

$linkDomain contains the domainId of the domain in the first table.

How do i get $linkDomain to display the domain name in first table that matches the set $domainId? So essentially in my example a value of “1” would display as “example.com”

I can get it to display the domain id, but not the domain name, whats the trick?

2

since this is the mysql forum and not the php forum, could you show us your actual query please?

i have a feeling you might be using one query for the domain, and then another query for the links, when it should really be a join query

3

Hi,

Thank you for your reply, and yes you are right; this is no php forum, but i thought the gist of it was there.

My query at the moment, goes like this:

'SELECT `link`,`desc`,`domainId` FROM `links` WHERE `catId`="'.$pageCat.'" && (`modelId`="1" || modelId="'.$pageModel.'") ORDER BY `domainId` ASC LIMIT ' . $from . ', ' . $max_results2;

to display this query i currently have this bit of code:

$linkLoop2 = '';
	 $linkAd = $row['link'];
	 $linkDesc = stripslashes($row['desc']);
	 $linkDomain = stripslashes($row['domainId']);
         $linkLoop = '
	&lt;ul class="bymodel-dllinks"&gt;
	';
	 $linkLoop2 .= '
	    &lt;li&gt;&lt;a href="'.$linkAd.'" target="_tab"&gt;'.$linkDesc.' '.$linkDomain.'&lt;/a&gt;&lt;/li&gt;
		';
	 $linkLoop3 = '	
	   &lt;/ul&gt;
	 ';

Now when echo’d off in order, it produces what i am aiming for. The only problem, like you say is that i have included no join, to link the “links” table to the “domains” table, which says that “1” is “example.com”. So I end up with $linkDomain being in my previous example “1”. I am trying to get the “1” to display as “example.com”

I have read a few tutorials on the different types of joins, and have been playing around with them, which brought me to this question. Im guessing that my issue has something to do with some kind of join :slight_smile:

I am a little bit stuck, and if someone could point me in the right direction here, I would appreciate it geatly!

Thanks.

4

please do a SHOW CREATE TABLE for both tables

5

links CREATE TABLE links (
id int(10) NOT NULL auto_increment,
catId int(2) NOT NULL,
modId int(3) NOT NULL,
domId int(4) NOT NULL,
link varchar(255) NOT NULL,
desc varchar(255) NOT NULL,
PRIMARY KEY (id),
KEY catId (catId,modId,domId,link,desc)
) ENGINE=MyISAM AUTO_INCREMENT=5003 DEFAULT CHARSET=latin1

domains CREATE TABLE domains (
domId int(4) NOT NULL auto_increment,
domain varchar(100) NOT NULL,
PRIMARY KEY (domId),
KEY domain (domain)
) ENGINE=MyISAM AUTO_INCREMENT=305 DEFAULT CHARSET=latin1

6 
SELECT dom.domain
     , lnk.catId
     , lnk.modId
     , lnk.link
     , lnk.`desc`
  FROM domains AS dom
INNER
  JOIN links AS lnk
    ON lnk.domID = dom.domID
   AND lnk.catId = $pageCat  -- no quotes 
   AND lnk.modId IN ( 1 , $pageModel ) -- no quotes
 WHERE dom.domID = $domainId -- no quotes
ORDER 
    BY lnk.id LIMIT $from , $max_results

this retrieves the specific categor and model links from the given (single) domain

7

Thank you kindly for the thoughts, although it was not exactly what i needed, you did however manage to get me on track!!

I now have this query:

$linkQuery2 = 'SELECT l.link 
              , l.desc 
              , l.domId 
              , d.domain 
              FROM links l 
              LEFT JOIN domains d 
              ON d.domId = l.domId 
              WHERE l.catId="'.$pageCat.'" 
              && (l.modId="1" || l.modId="'.$pageModel.'") 
              ORDER BY d.domain 
              ASC LIMIT ' . $from . ', ' . $max_results2.'
              ';

Which works exactly as I had intended it to, when echo’ing the variables I now get example.com to display instead of the 1 :lol:

I am over the moon, THANK YOU!!!

8

it’s a shame that my query did not get you to stay on the right track – i had changed a couple of things, and you changed them back :slight_smile:

first of all, it has to be an INNER JOIN

with a LEFT OUTER JOIN, you are anticipating that there will be links which have a domId that doesn’t exist in the domains table

which doesn’t sound right, if you ask me

secondly, i specifically changed your use of && and ||, which are non-standard sql, to AND and OR, which of course are standard sql

actually, i went further and replaced the two OR conditions like this –

AND l.modId IN ( 1 , '.$pageModel.' )

note that when you supply a value to be compared with a numeric column (like an Id column), then that value should be a numeric value, not a string, which is why i commented “no quotes” in several places

make sense?

9

Hi there,

Yes, thank you, that does make sense, a bit atleast :slight_smile:

I have a few questions though…

I now understand the difference between standard and non standard sql, but what is the importance in using the standard version? I ask this because the way I see it, its like old vs new? Whereas the old way of doing it is more recognised, and i guess would be able to operate on many different servers using different versions of sql? But if the server i currently use supports the nonstandard way, i mean, could that make an effect at all, unless i one day change servers, or in my case my host?

The problem was though, i your code did not orignally work for me, so i played with it, and took it apart and put it back together, for over an hour. Eventually i decided to make my own join, which eventually worked for me as All i really needed to in the end was the variable &linkDomain. I changed it to call domain and it worked :slight_smile:

In anycase though, I would like to hear your opinion on the way i that im making up this little query. I have changed the left outer join to a inner join now, i understand why too, even though this is mostly chinese to me.

If you’d like to check out what i was talking about, here is the link to my test page so far: free4blackberry - Free BlackBerry Curve 8520 Application Downloads

Thanks!

10

actually it’s the other way around – the non-standard way, supported only by mysql, won’t work on any other database systems

11

Alright, now that makes a lot of sense. But I mean, up until 3 weeks ago all I could tell you about mysql is that is it a type of database, so i’ve come quite a way since then.

I guess in my case though, i’d need to learn about other types of databases first :slight_smile:

The problem I had was simple syntax errors, I don’t really know how to put your code together into a php query and was having trouble with that.

Your point does make sense though, It is something I am going to have to put some thought into!

12

I have another question. I edited the above query a bit and put it to work on a different use. I am using it now on a download page. If a download item has a $modId of x it will match x from my models table and display its value.

An example of this would be, 2 = 8520 or 3 = 8900.

Now my question is this, Sure i can now get 8520 to display where the 2 is. But I want to have 2 $modId’s. Whereas I can have 2 values e.g. “2,3” and have it display “8520,8900”.

How could I accomplish something like that? I tried to set the values as “2,3” but it will only display the first value, being “8520”.

13

give up this idea

any time you store multiple values in a single column, you are making a design error

14

Argh!! Are you trying to tell me this is impossible?

Surely there is some smart way to work around this? Or better yet, a better design.

It seems so simple… One peice of software can work on x unique devices. I want to display those devices :frowning:

You’re bursting my creative bubble here, lol :slight_smile:

15

Hi,

Its not impossible, you’re simply not following the database normalization. Please check this for more info: Database normalization - Wikipedia, the free encyclopedia

Basically you need to have a seperate table and store each single record instead of adding comma seperated values in a single record.

Thanks.

16

Well that does make sense.

I currently have 2 tables. One contains information about the peice of software being downloaded. One of its possible values is modId, this matches the model value of the model in the next table.

The other contains model values. Eg modId = 1 model = 8520, modId = 2 model = 8900, modId = 3 model = 9000, etc.

I want to assign different model values to any specific download. Whereas a download can work on a modId of 1, 2 and 3.

It seems I have the models table set up correctly, but how would i set up my downloads table to be able to refer to that… So i can basically assign different model values to any specific download?

17

Hi,

Can you show table code for both of the tables ?

Thanks.

18

This is the first table that contains all the info about a specific download item:

CREATE TABLE `downloads` (
 `id` int(10) NOT NULL auto_increment,
 `catId` tinyint(2) NOT NULL,
 `modId` tinyint(3) NOT NULL,
 `name` varchar(21) character set latin1 collate latin1_general_ci NOT NULL,
 `image` varchar(180) character set latin1 collate latin1_general_ci NOT NULL,
 `imageAlt` varchar(50) character set latin1 collate latin1_general_ci NOT NULL,
 `desc` varchar(84) character set latin1 collate latin1_general_ci NOT NULL,
 `descMore` varchar(15) character set latin1 collate latin1_general_ci NOT NULL default 'More Details...',
 `link` varchar(180) character set latin1 collate latin1_general_ci NOT NULL,
 `page` varchar(180) character set latin1 collate latin1_general_ci NOT NULL,
 `devName` varchar(100) character set latin1 collate latin1_general_ci NOT NULL,
 `devDlPage` varchar(180) NOT NULL,
 `curVer` varchar(25) NOT NULL,
 `osReq` varchar(30) NOT NULL,
 `modSupport` varchar(255) NOT NULL,
 `baseSupport` varchar(50) NOT NULL,
 PRIMARY KEY  (`id`),
 KEY `catId` (`catId`,`modId`,`name`,`image`,`imageAlt`,`desc`,`descMore`,`link`,`page`)
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=latin1

This is the second table, that contains model info and its id:

CREATE TABLE `models` (
 `modId` tinyint(3) NOT NULL auto_increment,
 `model` varchar(20) NOT NULL,
 PRIMARY KEY  (`modId`),
 KEY `pollname` (`model`)
) ENGINE=MyISAM AUTO_INCREMENT=20 DEFAULT CHARSET=latin1

Im trying to find a way to put, eg. “2,3” into the modId field in the downloads table, and have it refer to the modIds of the items in the downloads table.

19

the modId in the downloads table is a single numeric value, so the problem of storing multiple values in a single column does not apply here

all you need is a join query

20

Well at the moment it is, with its length value set at 3, but I dont want it to be like that. It is useful when used with my links table, but in this case, i’d want to put more than one value into it, but in a nutshell, that would be bad?

I have read a bit about database normalisation, on this page Creating A Quick MySQL Relational Database Tutorial Using All Common Relationships - Web and dedicated hosting tutorials by Anchor, It discusses ‘SQL Joins/Table Relationships’ which is almost halfway down the page.

The writer uses an example that includes a dog and its breed. its under the section that discusses the One To Many relationship.

What happens if a dog is a mixed breed? Say its a hound terrier? What then? Would hound terrier specifcally need to be a breed option? He already has hound and terrier as options.

Should I be looking at a many to many option, where i include a linking table? That seems like alot more work that should simply be.