speckytwat

Ok, think I fixed it, by doing the following: SELECT DISTINCT LEFT (order_discount_code,3) AS vouchercode.... Then pulling $row['vouchercode'] to the variable to put into the option tags. So your DISTINCT LEFT did the trick. Thanks

Thanks, I tried changing the query to exactly as suggested but unfortunately that just makes the SELECT have blank entries in the dropdown: Correct, the order_discount_code can be ABC123, ABC456, ABC789 and so on, it's the ABC that I'm interested in outputting into the dropdown. I thought the blank entries might be because I was using $agencycode in the option tags, but I tried $vouchercode and that did the same...

Hi, anyone know how I can restrict the values displayed in a Select, taken from a MySQL query, which have been edited before output? The original values are all unique so doing a COUNT or DISTINCT in MySQL won't work. This is the PHP I'm using to pull and edit the values: $getusers = $mysqli->query("SELECT order_discount_code FROM orders ORDER BY order_discount_code ASC"); while ($row = $getusers->fetch_assoc()) { $vouchercode = $row['order_discount_code']; //These values are all unique but share common characters e.g the first three characters of the string $agencycode = mb_substr($vouchercode, 0, 3); //I'm doing this as I want to have just the first three characters show in the form Select that contains this query echo '<option>'.$agencycode.'</option>'; } Basically I just want to output unique instances of $agencycode, so if the first three characters might be ABC, DEF or GHI for all the various records, I want ABC to only show once in the Select, DEF to show once, GHI to show once etc. At the moment my code shows a whole long list of every instance. Anyone know how I can do this? Thanks!

Anyone have any ideas about how this can be achieved?

Yes, basically the query grabs all the jobs for a particular project. So Job 1 might be done by Person 1 and Job 2 might be done by person 2, and in each case the rates would be different. What I want to do is total up the various earnings for each of these jobs. So for example, let's say the query finds two jobs for the project. Job 1 is 1 hour and done by Person 1 who has a rate of 40, so therefore their earnings = 40. Job 2 is 1.5 hrs and done by Person 2 and their rate is 30, so the earnings = 45. So all I want to do is add together these calculated values, irrspective of person or rate, so in this case it would be 40 plus 45 = 85.

Hi, this is the query that pulls through the data: $getrate = $mysqli->query("SELECT * FROM rates WHERE StaffName LIKE '$category'"); The $category value is already specified as the name of the person, so the query grabs the data for that person (including the pay rate) from the table.

Hope someone can help, I am trying to add up all the values of a variable in a PHP while loop and have tried several approaches but none seem to work. Hopefully someone can point out what the correct method is. I've set up some code to pull the pay rate from a MySQL table, and then calculate that person's earnings based on the amount of time they spend on a particular job. That works fine: while ($row = $getrate->fetch_assoc()) { $staffrate = $row["Rate"]; $earnings = ($staffrate/3600) * $jobeventtimeseconds; $earningsformatted = number_format($earnings, 2); } However there may be a number of jobs for different people with different rates, so I want to add all of these up and output the total. So for example if I had values of 125.00, 35.50 and 22.75 I want to show 183.25 as the total. So I tried a few things I found on forums on the subject: $totalearnings = 0; foreach($earningsformatted as $key => $value) { $totalearnings += $value; } echo $totalearnings; That didn't work so I tried another method that someone suggested: $total=array($earningsformatted); echo array_sum($total); The 2nd method outputted just the last individual value, the 1st method didn't work at all. Does anyone have a working method that can be used to do this? Thanks

Hi, I'm trying to set up a page which first queries for mySQL record results matching a country that the user selects. This works fine, but in the event of there being no records for that country, I want it to look at another field, "Region" and pick the records matching that Region instead. For example, a user searches for "Australia" but there are no matching records. So, I want it to pick all the records for the region of Australasia, and display records for Australia, New Zealand, Papua New Guinea and so on. I had created the following: $query = "SELECT * FROM specialists WHERE Country LIKE '$country' ORDER BY SpecialistName"; // specify the table and field names for the SQL query //$query .= " limit $s,$limit"; $numresults=mysql_query($query); $numrows=mysql_num_rows($numresults); if ( $numrows == 0 ) { echo '<p>We don\'t have any results for specialists in countries near to yours at the moment. Please try <a href="specialists.php" style="text-decoration:underline;">searching a different country</a></p>'; } // get results $result = mysql_query($query) or die("Couldn't execute query"); // display the results returned while ($row= mysql_fetch_array($result)) { $region = $row["Region"]; $count++ ; } // next determine if s has been passed to script, if not use 0 if (empty($s)) { $s=0; } echo '<table width="600" class="cardisplay"><tr>'; $count = 1 + $s ; echo $region; // Build SQL Query $query2 = "SELECT * FROM specialists WHERE Region LIKE '$region' ORDER BY SpecialistName"; // specify the table and field names for the SQL query //$query .= " limit $s,$limit"; $numresults=mysql_query($query2); $numrows=mysql_num_rows($numresults); // get results $result = mysql_query($query2) or die("Couldn't execute query"); // display the results returned while ($row= mysql_fetch_array($result)) { $title1 = $row["ID"]; $specialistname = $row["SpecialistName"]; $address1 = $row["Address1"]; $address2 = $row["Address2"]; $address3 = $row["Address3"]; $address4 = $row["Address4"]; $address5 = $row["Address5"]; $postcode = $row["Postcode"]; $country = $row["Country"]; $region = $row["Region"]; $website = $row["Website"]; $email = $row["Email"]; $telephone = $row["Telephone"]; $businesstype = $row["BusinessType"]; //followed by echoing out the various data etc. etc. But the problem is that $region is always blank / empty in the cases where $query is empty, so I can't pull the value out and therefore $query2 is also empty... Any ideas?