SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Simple Query, Please Help!

    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:

    Code:
    $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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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:

    Code:
    '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:

    Code:
    $linkLoop2 = '';
    	 $linkAd = $row['link'];
    	 $linkDesc = stripslashes($row['desc']);
    	 $linkDomain = stripslashes($row['domainId']);
             $linkLoop = '
    	<ul class="bymodel-dllinks">
    	';
    	 $linkLoop2 .= '
    	    <li><a href="'.$linkAd.'" target="_tab">'.$linkDesc.' '.$linkDomain.'</a></li>
    		';
    	 $linkLoop3 = '	
    	   </ul>
    	 ';
    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

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

    Thanks.

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    please do a SHOW CREATE TABLE for both tables
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    Code:
    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
    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:

    Code:
    $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

    I am over the moon, THANK YOU!!!

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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

    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 --
    Code:
    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?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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

    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 --
    Code:
    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?
    Hi there,

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

    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

    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. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by blackberryfan View Post
    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?
    actually it's the other way around -- the non-standard way, supported only by mysql, won't work on any other database systems
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    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. #12
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #13
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by blackberryfan View Post
    Whereas I can have 2 values e.g. "2,3" and have it display "8520,8900".
    give up this idea

    any time you store multiple values in a single column, you are making a design error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  14. #14
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

    You're bursting my creative bubble here, lol

  15. #15
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by blackberryfan View Post
    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

    You're bursting my creative bubble here, lol
    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. #16
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #17
    SitePoint Evangelist Tapan's Avatar
    Join Date
    May 2005
    Location
    India
    Posts
    564
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by blackberryfan View Post
    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?
    Hi,

    Can you show table code for both of the tables ?

    Thanks.

  18. #18
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is the first table that contains all the info about a specific download item:

    Code:
    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:

    Code:
    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. #19
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  20. #20
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    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
    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.

  21. #21
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #22
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,323
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by blackberryfan View Post
    At this point i take it that I should rather have something like modId1, modId2, modId3, etc...
    no, not like that

    you need a separate table, and remove modId from the downloads table altogether
    Code:
    CREATE TABLE download_models
    ( downloadid INTEGER NOT NULL 
    , modelid TINYINT NOT NULL 
    , PRIMARY KEY ( downloadid, modelid )
    );
    suppose you had two downloads, frick and frack
    Code:
    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 --
    Code:
    INSERT INTO download_models VALUES
     ( 9 , 1 )
    ,( 9 , 2 )
    ,( 9 , 3 )
    ,( 37 , 1 )
    ,( 37 , 2 )
    ;
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  23. #23
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Ah, now you're talking...

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

    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. #24
    SitePoint Zealot blackberryfan's Avatar
    Join Date
    Nov 2010
    Location
    middleOnowhere, South Africa.
    Posts
    101
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Okay so I am stuck again. The concept I can grasp, but the query involved is mind boggling!

    I have this so far:

    Code:
    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:

    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. #25
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,708
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •