CODEWITHSUNDEEP
phpmysqljoin
Sam Street
Sam Street

Reputation: 306

Where have I gone wrong with my query?

I am quite a noob at PHP/MySQL an thus far have managed to get by, however, now I need to get information from two tables that are related. I have done much research on what I need to do, but can't seem to figure out where I have gone wrong.

QUESTION: How can I get the link from shortlinks table that is related to the shortlink in shortlink_analytics?

I get the error: Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given

My tables are as follows:

Shortlinks table

id

shortlink

link

createdTime

createdBy

shortlink_analytics table

shortlink

referrer

hitTime

userIP

userAgent

The code I have tried so far is:

$linecount = 1;
//$mostPop is where I think I have gone wrong and this is where the warning refers to
$mostPop = "SELECT shortlink, COUNT(shortlink) FROM shortlink_analytics JOIN shortlinks ON shortlink_analytics.shortlink = shortlinks.shortlink GROUP BY shortlink ORDER BY COUNT(shortlink) DESC LIMIT 10";
            $loadPop = mysql_query($mostPop);
            echo '<table id="middleIndex">';
            echo '<tr><td class = "overFlow"><h2>Most Popular Shortlink</h2></td></tr>';
            echo '<tr>';
            while($row = mysql_fetch_array($loadPop))
            {
                    echo '<td class = "overFlow">'.$row[1].' visits - <a href = "info.php?link='. $row['shortlink'] .'">hud.ac/' . $row['shortlink'] . '</a></td>';
                    echo '</tr>';
                    $linecount++;
            }
            echo '<tr id="indexMiddle"><td id="hand" class = "overFlow"><a onclick="indexMostPopular()">View More</a></td></tr>';
                echo '</table>';

I beleive that I do not have a thorough enough understanding behind how to use JOIN or the parameters that are required to form a successful join.

Upvotes: 0

Views: 72

Answers (2)

mamdouh alramadan
mamdouh alramadan

Reputation: 8528

you have shortlink in both tables which will make ambiguous case. Try this sql statement:

$mostPop = "SELECT shortlink_analytics.shortlink AS short, 
COUNT(shortlink_analytics.shortlink) AS shortcount,
(SELECT link FROM shortlinks 
WHERE shortlinks.shortlink = shortlink_analytics.shortlink) AS shLink
FROM shortlink_analytics 
JOIN shortlinks ON shortlink_analytics.shortlink = shortlinks.shortlink 
GROUP BY shortlink_analytics.shortlink 
ORDER BY COUNT(shortlink_analytics.shortlink) DESC LIMIT 10";

Upvotes: 1

Sahal
Sahal

Reputation: 4136

Try this (Run this query in mysql and if it is working add it in php script)

SELECT 
    shortlink_analytics.shortlink, COUNT(shortlink_analytics.shortlink)
FROM
    shortlink_analytics
INNER JOIN
    shortlinks ON shortlink_analytics.shortlink = shortlinks.shortlink
GROUP BY shortlink_analytics.shortlink
ORDER BY COUNT(shortlink_analytics.shortlink) DESC
LIMIT 10

And check mysql_effected_rows() function to check whether query will returns some row or not

Upvotes: 0

Related Questions