rocky48 Posted January 7, 2015 Share Posted January 7, 2015 (edited) I am querying my database to show the visit statistics for a particular week and it shows the number of visits for the countries, but does not display the country name. I have proved that the MySQL works by going into phpMyAdmin and pasting the query into SQL query tab, replacing the POST with 1, for week 1. I can't see why it is not displying the country. Here is the code: <?php include('connect_visits.php'); doDB7(); $WVisit_data="SELECT WeekNo15.WNo, WeekNo15.WCom, Countries.Country, Countries.CID, ctryvisits15.CVisits FROM ctryvisits15 LEFT JOIN Countries ON ctryvisits15.country = Countries.CID LEFT JOIN WeekNo15 ON ctryvisits15.WNo = WeekNo15.WNo WHERE ctryvisits15.WNo = '{$_POST['WeekNo']}' ORDER BY ctryvisits15.CVisits DESC"; $WVisit_data_res = mysqli_query($mysqli, $WVisit_data) or die(mysqli_error($mysqli)); $display_block =" <table width=\"20%\" cellpadding=\"3\" cellspacing=\"1\" border=\"1\" BGCOLOR=\"white\" > <tr> <th>Country</th> <th>Visits</> </tr>"; while ($WV_info = mysqli_fetch_array($WVisit_data_res)){ $Ctry = $WV_info['country']; $Visits = $WV_info['CVisits']; //add to display $display_block .=" <tr> <td width=\"10%\" valign=\"top\">".$Ctry."<br/></td> <td width=\"5%\" valign=\"top\">".$Visits."<br/></td> "; } mysqli_free_result($WVisit_data_res); mysqli_close($mysqli); ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd"> <!-- Design by Free CSS Templates http://www.freecsstemplates.org Released for free under a Creative Commons Attribution 2.5 License Name : Yosemite Description: A two-column, fixed-width design with dark color scheme. Version : 1.0 Released : 20091106 --> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta name="keywords" content="" /> <meta name="description" content="" /> <meta http-equiv="content-type" content="text/html; charset=utf-8" /> <title>1066 Cards 4U - Stats for country</title> <link href="style.css" rel="stylesheet" type="text/css" media="screen" /> </head> <body> <div id="wrapper"> <div id="menu"> <ul> <li class="current_page_item"><a href="index.php">Home</a></li> <li><a href="Links.html">Links</a></li> <li><a href="Verse_Menu.html">Verses</a></li> <li><a href="Techniques.html">Techniques</a></li> <li><a href="blog.php">Blog</a></li> <li><a href="Gallery.html">Gallery</a></li> <li><a href="contact.html">Contact</a></li> <li><a href="AboutUs.html">About Us</a></li> <li><a href="stats1.html">Stats</a></li> </ul> </div><!-- end #menu --> <div id="header"> <div id="logo"> <h1><a href="http://www.1066cards4u.co.uk">1066 Cards 4U</a></h1> </div><!-- end #wrapper --> </div><!-- end #header --> <div id="page"> <div id="page-bgtop"> <div id="page-bgbtm"> <div id="content"> <h3>Statistics for Week Commencing <? echo $WkCom; ?> in 2015</h3> <div id="table"> <?php echo $display_block; ?></div> </div><!-- end #content --> </body> </html> Can you help please? Edited January 7, 2015 by rocky48 Quote Link to comment https://forums.phpfreaks.com/topic/293724-problem-displaying-data-from-mysql/ Share on other sites More sharing options...
Barand Posted January 7, 2015 Share Posted January 7, 2015 Case sensitivity!. $Ctry = $WV_info['country']; should be $Ctry = $WV_info['Country']; Are you really storing data in a separate table for each week? Quote Link to comment https://forums.phpfreaks.com/topic/293724-problem-displaying-data-from-mysql/#findComment-1501994 Share on other sites More sharing options...
rocky48 Posted January 7, 2015 Author Share Posted January 7, 2015 No! I have table that stores week nos viz. weeks 1 to 52. That table has a date against each week no. Each year will have a separate table. I am away from my PC at the moment I will check the script. Quote Link to comment https://forums.phpfreaks.com/topic/293724-problem-displaying-data-from-mysql/#findComment-1502003 Share on other sites More sharing options...
Solution rocky48 Posted January 7, 2015 Author Solution Share Posted January 7, 2015 Your observation alone did not solve the problem. I noticed that the field ctryvisits.country was not in the SELECT query. After adding that it worked. Thanks very much! when you are working on the same code sometimes you miss the most obvious. Quote Link to comment https://forums.phpfreaks.com/topic/293724-problem-displaying-data-from-mysql/#findComment-1502006 Share on other sites More sharing options...
Barand Posted January 7, 2015 Share Posted January 7, 2015 I noticed that the field ctryvisits.country was not in the SELECT query. After adding that it worked. Strange how it "worked" in PhpMyAdmin then Quote Link to comment https://forums.phpfreaks.com/topic/293724-problem-displaying-data-from-mysql/#findComment-1502008 Share on other sites More sharing options...
rocky48 Posted January 7, 2015 Author Share Posted January 7, 2015 I agree! But that was the only thing I changed. Do all fields that you want to use have to be in the query? Quote Link to comment https://forums.phpfreaks.com/topic/293724-problem-displaying-data-from-mysql/#findComment-1502038 Share on other sites More sharing options...
Barand Posted January 7, 2015 Share Posted January 7, 2015 You don't have to select a field that you are joining on, unless you also want to output it. Naturally, if you want to output a field it needs to be selected. Other cases vary with flavour of SQL. With MySQL you can GROUP BY or ORDER BY a field that isn't selected but others, like MS SQL SERVER, are less lenient and adhere to the standard and the fields must be selected. Quote Link to comment https://forums.phpfreaks.com/topic/293724-problem-displaying-data-from-mysql/#findComment-1502046 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.