|
PHP - Why Would No Rows Be Returned For A Select Distinct Query?
Hello folks,
In trying to improve the user experience for my first WebApp I have decided to create two new tables - one a master file to contain a list of all stores, and the second a master file to contain a list of all products that are normally purchased - and I would like to use the values from these two tables as lookup values in dropdown listboxes, to which the user can also add new entries.
As it turns out, I'm stuck on the very first objective i.e. to lookup/pull-in the distinct values from the master tables.
The problem I'm having is that the query seems to return no rows at all...in spite of the fact that there a records in the table, and the exact same query (when run within the MySQL environment) returns all the rows correctly.
Is there something wrong with my code, or how can I debug to see whether or not the query is being executed?
Objective # 2, which is to allow new values to be entered into the dropdown listbox, and then inserted into the respective table is certainly waaay beyond my beginner skills, and I'll most certainly need to some help with that as well..so if I can get some code/directions in that regard it will be most appreciated.
Thank you.
<?php
$sql = "SELECT DISTINCT store_name FROM store_master ORDER BY store_name ASC";
$statement = $conn->prepare($sql);
$statement->execute();
$count = $statement->rowCount();
echo $count;
// fetch ALL results pertaining to the query
$result = $statement->fetchAll();
echo "<select name=\"store_name\">";
echo '<option value="">Please select...</option>';
foreach($result as $row)
{
echo "<option value='" . $row['store_name'] . "'></option>";
}
echo "</select>";
?>
Similar TutorialsI insert data in mysql table row using multiple method : 1#2#3 . 1 is ID of country, 2 is Id of state, 3 is ID of town . now i have this table for real estate listings. for each list(home) i have country/state/town (1#2#3). in country table i have list of country - in country table i have list of state - in country table i have list of town. i need to The number of houses in country / state / town . my mean is : Code: [Select] USA [ 13 ] <!-- This Is equal of alabama+alaska+arizona --> ----Alabama [8] <!-- This Is equal of Adamsville+Addison+Akron --> -------Adamsville [2] -------Addison[5] -------Akron[1] ......(list of other City) ----Alaska [ 3 ] -------Avondale[3] ......(list of other City) ----Arizona [ 2 ] -------College[2] ......(list of other City) Lisintg Table : Code: [Select] ID -- NAME -- LOCATION -- DATEJOIN -- ACTIVE 1 -- TEST -- 1#2#3 -- 20110101 -- 1 2 -- TEST1 -- 1#2#3 -- 20110101 -- 1 3 -- TEST2 -- 1#3#5 -- 20110101 -- 1 4 -- TEST3 -- 1#7#6 -- 20110101 -- 1 Country Table : Code: [Select] id -- name 1 -- USA stats Table : Code: [Select] id -- countryid -- name 1 -- 1 -- albama 2 -- 1 -- alaska 3 -- 1 -- akron town Table : Code: [Select] id -- countryid -- statsid -- name 1 -- 1 -- 1 -- adamsville 2 -- 1 -- 1 -- addison 3 -- 1 -- 1 -- akron Thanks For Any Help. This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=322629.0 I have 2 queries that I want to join together to make one row
How can I stop duplication in the below code? Where do I implement the DISTINCT function? $sql="SELECT * FROM ((resource l inner join resource_skill ln on l.Resource_ID = ln.Resource_ID) inner join skill n on ln.Skill_ID = n.Skill_ID) WHERE First_Name LIKE '%" . $name . "%' OR Last_Name LIKE '%" . $name ."%' OR Skill_Name LIKE '%" . $name ."%'"; //-run the query against the mysql query function $result=mysql_query($sql); //-create while loop and loop through result set while($row=mysql_fetch_array($result)){ $First_Name =$row['First_Name']; $Last_Name=$row['Last_Name']; $Resource_ID=$row['Resource_ID']; //-display the result of the array echo "<ul>\n"; echo "<li>" . "<a href=\"a.php?id=$Resource_ID\">" .$First_Name . " " . $Last_Name . "</a></li>\n"; echo "</ul>"; } } Hi am having a few problems solving this code with select distinct clause. None of what i tryed works. Can anyone help please thanks. this is just some of the query's i tryed $query7 = yasDB_select("SELECT DISTINCT * FROM useronline WHERE id;"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE id;"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE ip;"); $query7 = yasDB_select("SELECT DISTINCT ip,timestamp FROM useronline WHERE id;"); $query7 = yasDB_select("SELECT DISTINCT id,ip,timestamp FROM useronline WHERE id;"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline;"); and again but without ";" $query7 = yasDB_select("SELECT DISTINCT * FROM useronline WHERE id"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE id"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline WHERE ip"); $query7 = yasDB_select("SELECT DISTINCT ip,timestamp FROM useronline WHERE id"); $query7 = yasDB_select("SELECT DISTINCT id,ip,timestamp FROM useronline WHERE id"); $query7 = yasDB_select("SELECT DISTINCT ip FROM useronline"); this is the code am working on. Code: [Select] $query7 = yasDB_select("SELECT DISTINCT * FROM useronline WHERE id;"); $visitors_online = $query7->fetch_array(MYSQLI_ASSOC); $visitors_online = $query7->num_rows; $query7->close(); visitors online : <?php echo $visitors_online;?><br/> This topic has been moved to MySQL Help. http://www.phpfreaks.com/forums/index.php?topic=343149.0 Hello. I am trying to display only one instance of records that have the same memberid in my db. I am using the following statement but it continues to show all of the records that have the same memberid. Any ideas what I may be doing wrong? Code: [Select] $sql = "select DISTINCT memberid, event, category, date, enddate, locality, location, address, city, state, zip, contact, phone, notes, doc1, doc2, doc3, doc4, doc5 from event where date >= '$datenow' ORDER by date ASC"; Thanks for any help! Hi guys, using the code below within an admin panel to create a drop down allowing the user to select the profiles they wish to assign to the record they're creating, problem we have is that once a record is created, if they need to edit it for what ever reason the selected profile option isn't sticking. I've played around with lots of variants of if existing_record to try and get it add selected="selected" into the code but failed at every attempt, any advice gratefully received. Code: [Select] <?php // List only breeder profiles in the database echo '<select name="profile" class="textinput noborder">'; echo '<option value="any">Any</option>'; $qryGetDistinctProfile = "SELECT * FROM profiles ORDER BY title ASC"; $resGetDistinctProfile = mysql_query($qryGetDistinctProfile,$connection) or die(mysql_error()); if(mysql_num_rows($resGetDistinctProfile) > 0){ $id = mysql_result($resProfile, 0, "id"); while ($row = mysql_fetch_assoc($resGetDistinctProfile)){ echo '<option value="'.$row['id'].'" >'.$row['title'].'</option>'; } } echo '</select>'; ?> I have 5 entries in a table Code: [Select] $sql = "select count(distinct columnName) from table"; $result = mysql_result($sql); $count = mysql_fetch_array($result); echo $count[0];The output is 5 as expected. Code: [Select] $sql = "select distinct columnName from table"; $result = mysql_result($sql); $count = mysql_fetch_array($result); echo $count[0];the ouput is the first file name as expected, however Code: [Select] echo $count[1];gives undefined offset 1, which does not make any sense. Can anyone explain why the offset 1 is undefined if the count is 5? So, I've been trying to get this query working and can't quite get it to work. I'm trying to get an "array" of distinct browsers from the database, but it's only showing one of them. There are 3 unique browsers in the table and only "Chrome 30" gets returned. Here is the query:
SELECT DISTINCT `browser` AS `unique_browsers`,
COUNT(DISTINCT `ip`) AS `unique_visitors`,
COUNT(DISTINCT `country`) AS `unique_countries`,
COUNT(`id`) AS `total_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Chrome%') AS `chrome_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Internet Explorer%') AS `ie_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Firefox%') AS `firefox_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Safari%') AS `safari_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` LIKE '%Opera%') AS `opera_count`,
(SELECT COUNT(`id`) FROM `table` WHERE `browser` NOT LIKE '%Chrome%' AND `browser` NOT LIKE '%Internet Explorer%' AND `browser` NOT LIKE '%Firefox%' AND `browser` NOT LIKE '%Safari%' AND `browser` NOT LIKE '%Opera%') AS `unknown_count`
FROM `table`
GROUP BY `browser`
Everything works properly except the line:Hello,
This is confusing me! - this code is finding 2 rows of data ($nrows is 2), but I'm only getting 1 row of data returned. On the second while loop I'm getting the following error message:
Warning: mysql_fetch_array() expects parameter 1 to be resource, boolean given in /home/website.com/test.php on line 26
$query ="SELECT * FROM Accounts WHERE (jaaLatitude = '' OR jaaLongitude = '')";
$result=mysql_query($query)
or die ("Connection to database table failed." . mysql_error());
$nrows=mysql_num_rows($result);
$i = 0;
if ($nrows > 0)
{
while($record = mysql_fetch_array($result, MYSQL_BOTH))
{
$jaaIndex = $record[jaaIndex];
$jaaEmail = $record[jaaEmail];
$jaaLocation = $record[jaaLocation];
$jaaLongitude = $record[jaaLongitude];
$jaaLatitude = $record[jaaLatitude];
echo "<br>test jaaEmail is $jaaEmail<br>";
$i = $i + 1;
}
}
As I say the first while loop is working, so I don't see what can be wrong with the qurery (it's not failing).
Thanks for your help,
S
I was just wondering when you run a mysql query what the easiest way to find out the number of rows returned. Just so I can do a number of results found on a search. Thanks. Hi: I have the foll. code. The table "Reports" has multiple records for a given value of CID in the Field CID. I'd like to be able to select only 1 of them so that a list of customers appearing in the Reports table is available for selection in the dropdown alphabetically. The foll. code does it but it doesnt list the Customers alphabetically. And when I use Join, the query doesnt run. I get a blank list . The Field CID is common to both tables- Reports and Customers. Could someone help me with the Join ? Thanks. Swat Code: [Select] <?php $sqlco = "SELECT DISTINCT CID FROM `Reports` "; $resultco = mysql_query($sqlco) or die (mysql_error() ) ; if ($myrowco = mysql_fetch_array($resultco) ) { do { $cid = $myrowco["CID"]; $sqlrep = "SELECT * FROM `Customers` WHERE `CID` = '$cid' " ; $resultrep = mysql_query($sqlrep) or die (mysql_error() ) ; $myrowrep = mysql_fetch_array($resultrep); $company = $myrowrep["Company"]; printf("<option value=%d> %s , %s", $myrowco["CID"], $myrowrep["Company"], $myrowco["Mdate"]); } while ($myrowco = mysql_fetch_array($resultco)); } else { echo "No records found." ; } ?></select></a> What i tried was this : Code: [Select] <?php $sqlco = "SELECT DISTINCT CID FROM `Reports` r JOIN `Customers` c WHERE r.CID = c.CID ORDER BY c.Company asc "; $resultco = mysql_query($sqlco) or die (mysql_error() ); if ($myrowco = mysql_fetch_array($resultco) ) { do { printf("<option value=%d> %s ", $myrowco["CID"], $myrowco["Company"]); } while ($myrowco = mysql_fetch_array($resultco)); } else { echo "No records found." ; } ?> I need to add likes_username to this query and use a DISTINCT on it.
It currently counts how many likes a status has but in the table there are some statuses with multiple likes from the same username.
SELECT s.*, COUNT(l.likes_location_id) AS likeCount FROM stream AS s LEFT JOIN likes AS l ON ( l.likes_location_id = s.stream_id ) GROUP BY s.stream_id ORDER BY s.stream_id DESC LIMIT 50Many thanks, Hi Please take a look at my code snippet. I don't know how to get the number of rows returned from a MySQL stored procedure so that I can handle it accordingly. Any ideas please? Thanks Am currently using this code, and actually thinking about it should split the query to an include in order to allow for other database drivers, on the chance we may decide to ditch the old MySQL. But I digress from the question. Code: [Select] include($lib."dbconfig.php"); $q = 'SELECT * FROM file WHERE this = "'.$that'"; $result = mysql_query($q); if (mysql_num_rows($result) > 0) If the query doesn't pick up a row I'm getting this error yo, Warning: mysql_num_rows() expects parameter 1 to be resource, boolean given in D:\webpages\*\*\admin.php on line 553 So what would be a better way of testing if the query was successful? Have a web page that displays a number of update forms depending on the amout of records that get returned from a mysql query, if query returns 4 records then the page will display 4 identical forms, the trouble i'm having is getting the each of the 4 forms to display a different record as at the moment they all show just first record of query. I have played around with loops but not getting anywhere which may be due to my limited knowledge code for page is below Code: [Select] <?php $numberofrow =mysql_num_rows($Recordset1); for($counter = 1;$counter<=$numberofrow;$counter++){ ?> <label for="hometeam2"></label> <form id="form1" name="form1" method="POST" action="<?php echo $editFormAction; ?>"> <label for="hometeam"></label> <input name="fixid" type="text" id="fixid" value="<?php echo $row_Recordset1['FixtureID']; ?>" /> <input name="hometeam" type="text" id="hometeam2" value="<?php echo $row_Recordset1['hometeamname']; ?>" /> <label for="homescore"></label> <input name="homescore" type="text" id="homescore" value="<?php echo $row_Recordset1['homescore']; ?>" /> <label for="awayscore"></label> <label for="fixid"></label> <input name="awayscore" type="text" id="awayscore" value="<?php echo $row_Recordset1['awayscore']; ?>" /> <label for="awayteam"></label> <input name="awayteam" type="text" id="awayteam" value="<?php echo $row_Recordset1['awayteamname']; ?>" /> <input type="submit" name="update" id="update" value="Submit" /> <input type="hidden" name="MM_update" value="form1" /> I'm trying to develop some php that will execute a MySQL query and move the results into XML. In this particular instance, only one record is expected to be returned, and that one row will contain all the customer data, such as last name, first name, address1, etc. While I can get my code to work and create the xml if I hard-code in all the keys (MySQL column names), I would like it to dynamically step through all the keys/MySQL column names so that if the db changes later (add more columns, for instance) the code generating the XML won't need to be altered. It should take the MySQL column name, create an element based on the column name, then create a text node and insert the value. Currently I'm just at the spot of testing by echoing the appropriate key/value pairs. The code is stopping after the first key/value without going through the rest of the columns. What do I need to do in order to get it to dynamically step through each column and get the key/value? Here's the relevant code: if($_POST['buscode']) {echo "buscode is: ".$_POST['buscode']."<HR>";} else {echo "ERROR WITH RETRIEVING BUSCODE<HR>";} $buscode = $_POST['buscode']; $query = "SELECT * " ."FROM customers " ."WHERE buscode = '$buscode'"; echo "Query will be: ".$query."<HR>"; $result = mysql_query($query); if($result){echo "Success retrieving data from ats_ajax.<HR>";} else{echo "Error retrieving data from ats_ajax\; MySQL error is:".mysql_error($result)."<HR>";} //////////////////////////////////////// // Let's test getting the keys and values from the array.... /////////////////////////////////////// while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) { echo "This is step ".$i."<BR>"; $key = key($row); echo "Key is: ".$key."<BR>"; echo "Value is: ".$row[$key]."<BR>"; $i++; }//close of while Search not functioning. The drop down box to search on Club_Name and Email is blank, clicking search results unchanged ... all suggestions appreciated ... newbie at php. Thanks. <!-- Code below is not functional, trying to query search results, search box with drop down box for two fields--> <table width="50%"> <tr> <td height="30" align="left" bgcolor="#FFFFFF" class="form1" > {$letter_links} </td> <td height="30" align="right" bgcolor="#FFFFFF" class="form1" > <input type=hidden name="sorter" value="{$sorter}"> <table><tr> <form name="search_form" action="{$form.action}" method="post"> <td class="form1" ><input type="text" name="search" value="{$search}"></td> <td class="form1" ><select name="s_type" style=""> {section name=s loop=$types} <option value="{$smarty.section.s.index_next}" {if $types .sel}selected{/if}>{$types .value}</option> {/section} </select></td> <td class="form1"><input type="button" value="{$button.search}" class="button" onclick="javascript: document.search_form.submit();" name="search_submit"></td> </form> </tr></table> </td> </tr> </table> <!-- Code below successfully displays query results --> <form method="post" action="{$file_name}?sel=approve" enctype="multipart/form-data" name="form1" onsubmit="foo(); return false;"> <table cellspacing="2" cellpadding="10" border="1"> <tr align="center"><td>Num</td><td>Club Name</td><td>Login</td><td>Country</td><td>Region</td><td>City</td><td>Address</td><td>Web site</td><td>Email</td><td>Contact Name</td><td>Contact Phone</td><td>Swinging</td><td>Alcohol</td><td>Food</td><td>Entertainment</td><td>Fees</td><td>Approved/<br/>Rejected</td><td>Actions</td></tr> {foreach item=item from=$contest key=key name=foo} <tr align="center"><td><input type="hidden" name="all_approves[{$item.id}]" value="{$item.id}"/>{$key+1}</td><td>{$item.name}</td><td>{$item.login}</td><td>{$item.country_name}</td><td>{$item.region_name}</td><td>{$item.city_name}</td><td>{$item.address}</td><td>{$item.web_site}</td><td>{$item.email}</td><td>{$item.contact_name}</td><td>{$item.contact_phone}</td> <td> {math equation="x - 1" x=$item.swinging assign=index_pos} {$xml_swing[$index_pos].value}</td> <td>{math equation="x - 1" x=$item.alcohol assign=index_pos}{$xml_alco[$index_pos].value}</td> <td>{math equation="x - 1" x=$item.foot assign=index_pos}{$xml_foot[$index_pos].value}</td> <td>{math equation="x - 1" x=$item.entertainment assign=index_pos}{$xml_entertainment[$index_pos].value}</td> <td>{math equation="x - 1" x=$item.fees assign=index_pos}{$xml_fees[$index_pos].value}</td><td> <span> {if $item.is_approved eq '1'} Approved {else} Rejected {/if}</span></td><td align="center"><a href="{$file_name}?sel=edit&id_content={$item.id}">[ Edit ]</a><br/><a href="{$file_name}?sel=delete&id_content={$item.id}">[ Delete ]</a><br/><a href="{$file_name}?sel=approve&id_content={$item.id}">[ Approve ]</a><br/><a href="{$file_name}?sel=reject&id_content={$item.id}">[ Reject ]</a></tr> {/foreach} </table> </form> Say I do a query from a database that only asks for one field in return (which always has a number in it). How do you write the PHP to take the first number returned and add it to the next number returned (NOTE: There may be more than two numbers returned, but for now, I'm always dealing with two) For example, say I do a query that returns the number 27 first and then the number 10 second. FYI, these numbers will be available in the variable $diff['result']. So the code after the query would look something like this, but obviously i'm struggling with the part in comments... Code: [Select] if (!$numbers) { die("Database query failed: " . mysql_error()); } else { while ($diff = mysql_fetch_array($numbers)) { //this would bring back 27 first, in the variable $diff['result'] and then on the 2nd loop through, it would bring back 10 in the $diff['result'] variable //ultimately, i just want the difference between the numbers (which in the case would be 17). It's just a simple addition, so Im obviously an idiot because I can't figure out the syntax for adding the first returned number to the next returned number:) } } |
|||||||