PHP - Moved: Select Distinct Query Question

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?

This topic has been moved to MySQL Help.

http://www.phpfreaks.com/forums/index.php?topic=348309.0

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 50

Many thanks,  

I 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=328883.0

I am trying to print the list of a table which I requested with "SELECT DISTINCT" as below

Code: [Select]
$db_connect = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME);

$sql_get = "SELECT DISTINCT category FROM con";

$sql_run = mysqli_query($db_connect, $sql_get) or mysqli_error($db_connect);

$sql_assoc = mysqli_fetch_assoc($sql_run);

What is now needed to print the list of the table data by this conditions?

I tried the while loop, but I seem to approach wrong, and get endless loops or errors.

Hello all,   Based on the suggestion of you wonderful folks here, I went away for a few days (to learn about PDO and Prepared Statements) in order to replace the MySQLi commands in my code. That's gone pretty well thus far...with me having learnt and successfully replaced most of my "bad" code with elegant, SQL-Injection-proof code (or so I hope).   The one-and-only problem I'm having (for now at least) is that I'm having trouble understanding how to execute an UPDATE query within the resultset of a SELECT query (using PDO and prepared statements, of course).   Let me explain (my scenario), and since a picture speaks a thousand words I've also inlcuded a screenshot to show you guys my setup:   In my table I have two columns (which are essentially flags i.e. Y/N), one for "items alreay purchased" and the other for "items to be purchased later". The first flag, if/when set ON (Y) will highlight row(s) in red...and the second flag will highlight row(s) in blue (when set ON).   I initially had four buttons, two each for setting the flags/columns to "Y", and another two to reverse the columns/flags to "N". That was when I had my delete functionality as a separate operation on a separate tab/list item, and that was fine.   Now that I've realized I can include both operations (update and delete) on just the one tab, I've also figured it would be better to pare down those four buttons (into just two), and set them up as a toggle feature i.e. if the value is currently "Y" then the button will set it to "N", and vice versa.   So, looking at my attached picture, if a person selects (using the checkboxes) the first four rows and clicks the first button (labeled "Toggle selected items as Purchased/Not Purchased") then the following must happen:   1. The purchased_flag for rows # 2 and 4 must be switched OFF (set to N)...so they will no longer be highlighted in red. 2. The purchased_flag for row # 3 must be switched ON (set to Y)...so that row will now be highlighted in red. 3. Nothing must be done to rows # 1 and 5 since: a) row 5 was not selected/checked to begin with, and b) row # 1 has its purchase_later_flag set ON (to Y), so it must be skipped over.   Looking at my code below, I'm guessing (and here's where I need the help) that there's something wrong in the code within the section that says "/*** loop through the results/collection of checked items ***/". I've probably made it more complex than it should be, and that's due to the fact that I have no idea what I'm doing (or rather, how I should be doing it), and this has driven me insane for the last 2 days...which prompted me to "throw in the towel" and seek the help of you very helpful and intellegent folks. BTW, I am a newbie at this, so if I could be provided the exact code, that would be most wonderful, and much highly appreciated.   Thanks to you folks, I'm feeling real good (with a great sense of achievement) after having come here and got the great advice to learn PDO and prepared statements.   Just this one nasty little hurdle is stopping me from getting to "end-of-job" on my very first WebApp. BTW, sorry about the long post...this is the best/only way I could clearly explaing my situation.   Cheers guys!

case "update-delete":
	if(isset($_POST['highlight-purchased'])) 
		{
			// ****** Setup customized query to obtain only items that are checked ******
			$sql = "SELECT * FROM shoplist WHERE";
					for($i=0; $i < count($_POST['checkboxes']); $i++)
						{
							$sql=$sql . " idnumber=" . $_POST['checkboxes'][$i] . " or";
						}
					$sql= rtrim($sql, "or");
											
			$statement = $conn->prepare($sql);
			$statement->execute();
																						
			// *** fetch results for all checked items (1st query) *** //
			$result = $statement->fetchAll();

			$statement->closeCursor();
											
			// Setup query that will change the purchased flag to "N", if it's currently set to "Y"
			$sqlSetToN = "UPDATE shoplist SET purchased = 'N' WHERE purchased = 'Y'";
											
			// Setup query that will change the purchased flag to "Y", if it's currently set to "N", "", or NULL
			$sqlSetToY = "UPDATE shoplist SET purchased = 'Y' WHERE purchased = 'N' OR purchased = '' OR purchased IS NULL";

			$statementSetToN = $conn->prepare($sqlSetToN);
			$statementSetToY = $conn->prepare($sqlSetToY);
											
			/*** loop through the results/collection of checked items ***/
			foreach($result as $row)
				{
					if ($row["purchased"] != "Y")
						{
							// *** fetch one row at a time pertaining to the 2nd query *** //
							$resultSetToY = $statementSetToY->fetch();
															
							foreach($resultSetToY as $row)
								{															
									$statementSetToY->execute();																
								}
						}
					else
						{
							// *** fetch one row at a time pertaining to the 2nd query *** //
							$resultSetToN = $statementSetToN->fetch();
															
							foreach($resultSetToN as $row)
								{															
									$statementSetToN->execute();																
								}
						}
				}
				break;
		}

 CRUD Queston.png   20.68KB   0 downloads    

I have this code below:

Code: [Select]
members_count <= ".$diff." || members_count >= ".$defmemcount."
My test says $diff = 6 and $defmemcount = 11, members_count = 14

So how come this returns as if members_count isn't less or equal to 6 OR higher or equal to 11?

ok, so I need to know how I can perform an operation of $total equaling $price1 + $price2 etc.... if they are not empty in the database............... does this make sense?

Hi, Everyone! This my first time posting here. Anyways, I've been having problem getting the correct data from my database. I want to select the total_on/off_hours,exposure,plate_number, and terminal_status. But when I select the terminal_status, there are duplicates, I already used the DISTINCT function. It's a little hard to explain so, I'll just show you.   Here's a screen cap of my code trying to get just the terminal_status       What's the problem? What should I do? Any suggestions/help will be much appreciated.

i am unable to select total_comment from mysql table related to each post..   For detail information check http://stackoverflow...-query-in-mysql

Hi

I need to do a SELECT query like

$query = "SELECT name FROM myusers WHERE myidnum = ".$_SESSION['myid'].""; 

and put the results into an array like

$myusers = array("David","John","Lucy","Sarah");

But I cannot figure out how to do this

Can anyone help?

Thanks