PHP sql issues

Discussion in 'Web Design and Development' started by joecool85, Mar 13, 2008.

  1. joecool85 macrumors 65816

    joecool85

    Joined:
    Mar 9, 2005
    Location:
    Maine
    #1
    I am trying to change the sorting method of my tag cloud (http://www.addfaith.net/menu5.php) so that it sorts so the most tagged item is at the top. I can get it to sort this way in phpMyAdmin and it gives me this sql:

    SELECT COUNT( * ) AS `Rows` , `ID_TAG`
    FROM `smf_tags_log`
    GROUP BY `ID_TAG`
    ORDER BY `Rows` DESC
    LIMIT 0 , 30

    Right now my php is using this sql query:

    $query = "SELECT t.tag AS tag, l.ID_TAG, COUNT(l.ID_TAG) AS quantity
    FROM smf_tags as t, smf_tags_log as l WHERE t.ID_TAG = l.ID_TAG
    GROUP BY l.ID_TAG
    ORDER BY l.ID_TAG DESC LIMIT 20";

    When I changed it to:

    $query = "SELECT COUNT( * ) AS `Rows` , `ID_TAG`
    FROM `smf_tags_log`
    GROUP BY `ID_TAG`
    ORDER BY `Rows` DESC
    LIMIT 0 , 30";

    ...I got nothing, it didn't spit out any data at all. Any help?


    **EDIT**
    Here is the code for context:
    PHP:
    <html>
    <head><title></title>
    <link rel="stylesheet" type="text/css" href="style.css" />
    </head>
    <body bgcolor="fafad2">

    <center>
    <font size="+4" color="rgb(128,0,0)"><b>+</b></font>  <font size="+4" color="rgb(128,0,0)">Faith</font><br>
    </center>
    <br><br>

            
    <ul class="links">
    <li><a href="main.php" id="front" title="Front Page" target="main"><span>Front Page</span></a></li>
    <li><a href="http://forum.addfaith.net" id="forum" title="Forum - Discuss religious matters, ask questions, get answers" target="main"><span>Forum</span></a></li>
    <li><a href="http://wiki.addfaith.net/index.php?n=WorshipWiki.WorshipWiki" id="wiki" title="Worship Wiki - Find a new place of worship, or add your own if it isn't there yet!" target="main"><span>Worship Wiki</span></a></li>
    <li><a href="http://www.addfaith.net/about.html" id="about" title="About AddFaith" target="main"><span>About AddFaith</span></a></li>
    </ul>

    <br><br><br>
    What's being talked about on AddFaith:<br><br>

    <?php

    $dbhost 
    "****"#Your database host

    $dbname "****"#Type your database name

    $dblogin "****"#Type your database username

    $dbpass "****"#Type your database password

    //$db_prefix = "smf_";

    $connection mysql_connect("$dbhost""$dblogin""$dbpass");
    mysql_select_db ("$dbname");

    global    
    $db_prefix$scripturl$context;

    //Tag cloud from [url]http://www.prism-perfect.net/archive/php-tag-cloud-tutorial/[/url]
          
            
    $query "SELECT COUNT( * ) AS  `Rows` ,  `ID_TAG` 
            FROM  `smf_tags_log` 
            GROUP BY  `ID_TAG` 
            ORDER BY  `Rows` DESC 
            LIMIT 0 , 30"
    ;
          
        
    $result mysql_query($query);

          
    //$result = db_query($query, __FILE__, __LINE__);
             
          // here we loop through the results and put them into a simple array:
          // $tag['thing1'] = 12;
          // $tag['thing2'] = 25;
          // etc. so we can use all the nifty array functions
          // to calculate the font-size of each tag
          
    $tags = array();
          
          
    $tags2 = array();
          
          while (
    $row mysql_fetch_array($result))
          {
              
    $tags[$row['tag']] = $row['quantity'];
              
    $tags2[$row['tag']] = $row['ID_TAG'];
          }
          
          if(
    count($tags2) > 0)
          {
             
    // change these font sizes if you will
             
    $max_size 150// max font size in %
             
    $min_size 90// min font size in %
             
             // get the largest and smallest array values
             
    $max_qty max(array_values($tags));
             
    $min_qty min(array_values($tags));
             
             
    // find the range of values
             
    $spread $max_qty $min_qty;
             if (
    == $spread)
              { 
    // we don't want to divide by zero
                 
    $spread 1;
             }
             
             
    // determine the font-size increment
             // this is the increase per tag quantity (times used)
             
    $step = ($max_size $min_size)/($spread);
             
             
    // loop through our tag array
             
    $context['poptags'] = '';
             
    $row_count 0;
             foreach (
    $tags as $key => $value)
             {
                
    $row_count++;
                 
    // calculate CSS font-size
                 // find the $value in excess of $min_qty
                 // multiply by the font-size increment ($size)
                 // and add the $min_size set above
                 
    $size $min_size + (($value $min_qty) * $step);
                 
    // uncomment if you want sizes in whole %:
                 // $size = ceil($size);
             
                 // you'll need to put the link destination in place of the #
                 // (assuming your tag links to some sort of details page)
                 
    $context['poptags'] .= '<a href="' 'http://forum.addfaith.net/index.php' '?action=tags;id=' $tags2[$key] . '" style="font-size: '.$size.'%"'.'target="main"';
                 
    // perhaps adjust this title attribute for the things that are tagged
                
    $context['poptags'] .= ' title="'.$value.' things tagged with '.$key.'"';
                
    $context['poptags'] .= '>'.$key.'</a> ';
                if (
    $row_count 5)
                {
                   
    $context['poptags'] .= '<br />';
                   
    $row_count =0;
                }
                 
    // notice the space at the end of the link
             
    }
          }
          
          


    echo 
    $context['poptags'];

    ?>

    </body>
    </html>
     
  2. Winterfell macrumors regular

    Joined:
    Apr 3, 2007
    Location:
    Tulsa, Oklahoma
    #2
    You do not need the ticks, and most people writing SQL queries do not use them. They're generated for compatibility reasons, but you should be able to remove them without consequence.

    So your query would look something like this instead:
    PHP:
    $query "SELECT   COUNT(*) AS Rows, ID_TAG
              FROM     smf_tags_log
              GROUP BY ID_TAG
              ORDER BY Rows DESC
              LIMIT    0, 30"
    ;
    See if that works first.

    You might also try running the query like so, and see if you get any information back if it does fail.
    PHP:
    $result mysql_query($query) or exit('Query Failed: ' mysql_error());
     
  3. joecool85 thread starter macrumors 65816

    joecool85

    Joined:
    Mar 9, 2005
    Location:
    Maine
    #3
    Thanks for your help, Winterfell. I just got a chance to try the code.

    When I put it what you suggested, I get nothing to come out of it. I got rid of everything in my code below the $result line and put in at the bottom "echo $result;" and that gave me "Resource id #2"

    Any idea why it would spit that out?
     
  4. Winterfell macrumors regular

    Joined:
    Apr 3, 2007
    Location:
    Tulsa, Oklahoma
    #4
    Hrm. That sounds like the query is executing successfully.

    Your PHP might default to no error reporting (which is technically good for security on things like production web sites).

    Does anything change if you add the following line to the top of your code?
    PHP:
    error_reporting(E_ALL);
    E_STRICT is also one step above E_ALL regarding how much information it reports.

    Try both of those and see if you get any more information. =)
     
  5. zimv20 macrumors 601

    zimv20

    Joined:
    Jul 18, 2002
    Location:
    toronto
    #5
    are you trying to grab rows or just a count? your first query is grabbing rows, and in your second you're just asking for the count.
     
  6. Scuzzeh macrumors newbie

    Joined:
    Mar 21, 2008
    Location:
    Scotland
    #6
    I think what you're trying to do is going to require nested queries. My SQL is a little rusty so I'll try and piece it together as best I can :)

    Code:
    SELECT COUNT(ID_TAG) AS Rows
    FROM smf_tags_log
    WHERE ID_TAG = (SELECT DISTINCT ID_TAG FROM smf_tags_log)
    GROUP BY ID_TAG
    ORDER BY Rows DESC;
    
    Something like that anyway I think.
     
  7. joecool85 thread starter macrumors 65816

    joecool85

    Joined:
    Mar 9, 2005
    Location:
    Maine
    #7
    Boy does that change things! I got this:

    Notice: Undefined index: tag in /home/.java/joecool85/addfaith.net/menu5.php on line 65

    Notice: Undefined index: quantity in /home/.java/joecool85/addfaith.net/menu5.php on line 65

    Notice: Undefined index: tag in /home/.java/joecool85/addfaith.net/menu5.php on line 66

    Notice: Undefined index: tag in /home/.java/joecool85/addfaith.net/menu5.php on line 65

    It kept going, but you get the idea.

    I'm trying to have it sort by the rows. The rows in that table count how many times something has been tagged and I was the things with the most tags to show up at the top.

    When I tried that, I got this error:

    Warning: mysql_query() [function.mysql-query]: Unable to save result set in /home/.java/joecool85/addfaith.net/menu6.php on line 50
    Query Failed: Subquery returns more than 1 row
     
  8. Scuzzeh macrumors newbie

    Joined:
    Mar 21, 2008
    Location:
    Scotland
    #8
    Yeah, it's just occurred to me that would happen...

    You could try pulling all of the tags into an array and then running a count on each of them and arranging it that way?

    Will have another think about the SQL in the morning as it's quite late over here and i'm sleepy.
     
  9. joecool85 thread starter macrumors 65816

    joecool85

    Joined:
    Mar 9, 2005
    Location:
    Maine
    #9
    Yeah, I'm thinking I may need to have it make an array as well. It's been long enough (3 years) since I took a course in PHP though, so if you have some help with that, that'd be great :)
     
  10. joecool85 thread starter macrumors 65816

    joecool85

    Joined:
    Mar 9, 2005
    Location:
    Maine
    #10
    Do I have to make the list with the SQL statement? Because in the code it already finds how many times each thing is tagged with the $value variable. If you scroll over a tag it says "this has been tagged X amount of times." Can I make a list of tags using data from the SQL statement, but sort it with $value ?
     

Share This Page