I found an answer using the GROUP BY clause:
SELECT * FROM goethe_search GROUP BY (SUBSTRING(msg_text FROM 10 FOR 75));
Easy when you know how :D

Hello :D
I am using the twitter API to perform a search for a certain author, and storing the results in a database so that I can perfrom an action on each entry. However, there are many duplicate results that have small differences at the beginning of the string or at the end that I do not want to respond to. I have managed to select all the distinct entries based on a substring:
SELECT DISTINCT (SUBSTRING(msg_text FROM 10 FOR 75)) AS res FROM goethe_search;
...which gives me the dinstinct substrings but not the other fields that corelate to the distinct results. I tried this:
SELECT * FROM (SELECT DISTINCT (SUBSTRING(msg_text FROM 10 FOR 75)) FROM goethe_search) AS res;,
and this:
SELECT DISTINCT (SUBSTRING(msg_text FROM 10 FOR 75)) AS res, id FROM goethe_search GROUP BY id;,
but they both give me all the results and I lose the distinct aspect. Can anyone point me in the right direction?
Thanks in advance :D

It's perfect!
Thank you Pritaeious, as usual, a fast and concise solution :D

For a moment I thought I was being REALLY stupid :D
Goedendag Pritaeious, but that only returns 1000 results even if there are 25000 matches. I want to make an initial query that will tell me the exact amount of matches and no data, and then a second query collecting the data with a limit of 1000, or one that does both if it is possible.
Thanks for your speedy consideration :D

Hello :)
I'm working with a database of postcodes. I have an existsing query that returns the postcodes within a certain radius:

select *, acos(cos(51.496502411798 * (PI()/180)) *cos(-0.13982862499836 * (PI()/180)) *cos(lat * (PI()/180)) *cos(lng * (PI()/180))+cos(51.496502411798 * (PI()/180)) *sin(-0.13982862499836 * (PI()/180)) *cos(lat * (PI()/180)) *sin(lng * (PI()/180))+sin(51.496502411798 * (PI()/180)) *sin(lat * (PI()/180))) * 3959 as Dist from SW having Dist < 1 order by Dist

Dist 1 is the number of miles for the search radius.

I am making a script that will allow the user to enter a postcode and return a list of postcodes in the given radius. There can be over 20,000 results of which only 1000 will be displayed. Currently, I get all results into a PHP array and use the count() function to return the total, and just display upto 1000.
I would like to make the query produce only a count of relevant matches, and then make a follow up query with a limit of 1000 which will stop the unneccesary collection of data over 1000 rows.

Thanks for your time :D

Ah I see, line 20, change:
$values = $max; to $values = $row;.
Also, I think line 27 will give a problem:
$this->inputBookingRef($values, true), because the
inputBookingRef() function takes only one parameter,
as it is in the commented out line 28:
Hope this helps, happy coding :D

if(isset($_POST['id']) && ($row['bankID'] == $_POST['id']))
if(isset($row['bankID']) && ($row['bankID'] == $_POST['id']))
depending which key is not set.

It's good that you have all errors showing.

Yep, that's works :D
I've used this for years but always hated it because I didn't understand it. Now I guess that although the original function returns false, there is nowhere calling for a return value as there would be if you wrote:
$bool = doFunction(this); if ($bool){...
so by writing:
return doFunction()
The 'return false' returned from the function has some where to return to?

I tested without return false; initially. The alert alerted and the colours coloured, and when I clicked 'OK', the form went a head and submitted anyway, in FF, Chrome and IE. Yet when I added return false;, it stopped the form submitting, also in all three browsers. There is probably a proper way of acheiving this, and I would like to know what it is :D

I once put all the UK postcode blocks with their accompanying longitudes/latitudes and northings/westings into a MySQL database, I dont remember exactly how many records, but I think it was more than 20,000. I set up a script that would insert 10 to each query, and 100 queries, and the page would then reload and go on with the next 100X10 block. The whole thing took over 15 mins to complete, but it was all there at the end of it.
Would you really need to recreate the whole database everytime? Could you not only take the new entries to the CSV file since your last update? Maybe check DB for last entry, search CSV for that entry, and grab all previous lines and insert them to DB? How many entries are likely to have backlogged between each user visit? Could the user visit itself not trigger the update process? How often is the CSV file written to? Is data being overwritten or appended?
I love challenges like this, ones you can get your teeth into, rather than making a few bits of text dance :D

I see the problem:
function validateForm(Qform) expects a parameter passed with the function call, but you pass nothing:
change it to:
onSubmit="validateForm(this); return false;"
and it should work okay.

I mentioned two errors, did you fix them both?

What do you want to happen if the email address already exists?

Okay, I'm lost now :)
Can you repost your updated code and as clearly and simply as possible tell me what exactly what you want to achieve.
You are collecting data in one place and hoping to show it in another place, you need to check each place where the data is transferred. It's like a relay race, we need to look at each changeover and see who is dropping the baton.

This hurts me more than it hurts you, but...
Let me google that for you!

phorce commented: Brilliant! +6

Line 32: $sql="INSERT INTO tbl_candidate(email_id) VALUES('$email_id1'); has a missing quote before the semi-colon.
Line 39: else{ is missing the preceding curly brace.
Fix those, see what happens.

Your database section looks a little mamlformed, specifically this line:
while ($query !=-1 && $row = mysql_fetch_assoc($query)) {
I would guess that the previous line:
$values = mysql_fetch_assoc($query);
already collected the necessary information, so if this line:
echo print_r($values, true);
prints the correct information, I would try changing your code at line 12(ish) to:

if(!$res = mysql_query("SELECT MAX(ballpark_details_booking_ref) as max_booking_ref FROM `ballpark_details`")){//error check the db interaction.
} else {//we are selecting 'MAX', there will only be one row
    $row = mysql_fetch_assoc($res);//no need for a while loop to collect one line.
    $max = $row['max_booking_ref'];

...does it work as intended?

Welcome to Daniweb albertsibanda9 :)
The long answer:
Read This Before Posting A second Question
The short answer:
Post your code before LastMitch gets hold of you :D

<M/> commented: well said... +5
LastMitch commented: LOL - that's a good one Adam! =) +6
diafol commented: Hilarious +14

but i want to show the error in such a way that name of the tables couldn't be shown...

Is that all?

$query = mysql_query("DELETE FROM table_2 WHERE tchr_id='".$id."'") or die("Oops, we have a problem, please contact the administrator.");

Change message to suit.

@diafol Haha! Looking at your funny man links, I guess it could be 'David Hill' with a Welsh accent. I had not heard of Vanilla JS and was impressed with the performance stats, but as you mentioned, cross browser compatibility is a real pain with Javascript and jQuery separates the developer from this concern. Also, the jQuery UI looks like it will speed development of User Interfaces like the one I need to administer my quotes database.

@diafol Ahhhh, so it is JSON format, that's a tidy solution.

check for result

    if($result = mysql_query("SELECT * FROM table")){
        while ($row = mysql_fetch_array($result)){
            // don't echo immediately, store in $string
            $string .= '["' .$row[1]. '",' .$row[2].'],';
    // now $string has an extra comma...
    $string = substr($string, 0, -1);

use substr() to select all but last character, then echo the whole thing.

@dafodil It's high time I learnt jQuery!
I sussed the issue was not the duplicate name, but rather the use of:
div.innerHTML += "<textarea name='new_quote[]' />";
as when it grabbed the existing html it was collecting the textarea but not the user-added input. This was solved by adding a new textarea to the DOM rather than the above method, and I googled some jQuery assistance:

<script type="text/javascript" src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js"></script>
    <script type="text/javascript">
                var textArea = "<textarea name='new_quote[]' />";
                $("#div_quotes").append("\n<br />");

And the button to add a text area needed an id: <input type="button" value="Add Text Area" id="button_add_input">
I looked for a way to add the textarea without specifying the html as in something like $("#div_quotes").append(html.textarea), but could not find how to reference a textarea directly, so just added the HTML instead.

I have the same requirement so I wrote a few lines that would demonstrate the answer to your question and give me a base to work from.

if (isset($_POST['submitted'])){
    <script type="text/javascript">
        function addTextArea(){
            var div = document.getElementById('div_quotes');
            div.innerHTML += "<textarea name='new_quote[]' />";
            div.innerHTML += "\n<br />";
<form method="post">
<input type="text" name="text_new_author" /><br />
<div id="div_quotes"></div>
<input type="button" value="Add Text Area" onClick="addTextArea();">
<input type="submit" name="submitted">

I thought I would need to keep a total of how many text areas had been added to allow them to have an individual name; area_1, area_2, area_3 etc. However adding [] to the textarea name and using the same name creates an array of values, so no values are lost and a unique name is not needed, print_r($_POST) produces:
Array ( [text_new_author] => New Author [new_quote] => Array ( [0] => New quote 1 [1] => New quote 2 [2] => New quote 3 ) [submitted] => Submit Query )
But this solution causes another problem; if the user adds a new quote and then clicks the 'add text area' button the text entered into the first textarea disappears. I presume this is because a new textarea with the same name is added to the DOM, so maybe I will need a unique name for each added textarea after all :)

$alert_error = "<script type=\"text/javascript\"> alert('".mysql_error()."') </script>";
In the above line you are referencing 'mysql_error()', but it doesn't exist yet because you did not run the query that produces the error.
Also, you wanted Javascript to alert the value of 'mysql_error()':

if (!$query = mysql_query("DELETE FROM abc WHERE id ='$id'")){
    echo "<script type=\"text/javascript\"> alert('".mysql_error()."') </script>";

The above works when I test it.

Hey GraficRegret, welcome to Daniweb :)
Your description sounds like a typical use of PHP and MySql, but what help do you specifically need? Is there some code you need some assistance with? Are you looking for general considerations and tips from those who already walked that road?

There are probably better ways, but If I had to achieve it with existing knowledge, I would do something like this:

$wordString = $_POST['search'];
/* first test there are comma separated values */
if (strpos($wordString, ",")){
    /* split string into array of strings on the comma */
    $words = explode(",", $wordString);
    /* start the sql */
    $sql = "SELECT * FROM table WHERE";
    /* for each word update the query */
    foreach($words as $word){
        $sql.=" field LIKE '%$word%' OR";
    /* remove trailing 'OR' from query */
    $sql = substr($sql, 0, -3);
} else {
    /* NO comma separated values, build normal query */
    $sql = "SELECT * FROM table WHERE filed LIKE '%$wordString%'";

If the user enters 'one,two,three', the resulting query would be:
"SELECT * FROM table WHERE field LIKE '%one%' OR field LIKE '%two%' OR field LIKE '%three%'", otherwise a normal query is constructed.

There are probably better ways, but I would make a temporary database with mysql and store them in there for easier manipulation. I would write a script to insert a few hundred at a time, and add the records gradually to avoid PHP execution limit, with an echo for every insert to show me that it is still running.
Then I would run the query:
"SELECT * FROM table ORDER BY column ASC, limit 10"

Hey that's plenty info.
The line needs to be in the while loop (it will change every time), so put it under the line:

while($row = mysql_fetch_array($result)){
    $total = ( $row['field3'] - ($row['field1'] / $row['field2']) );
    //then in the place you want the total to show:
    echo "<td>".$total."</td>";

This presumes your column names really are 'field1', 'field2' and 'field3', if not adjust accordingly. Diafol's idea was good too, well worth investigating, but you would need to show your existing query for instruction on that front.

Probably can help, but need to see the rest of your code to understand why there is an error.

This is simple maths, use parentheses to force calculations in correct order:
field3 minus (field1 divided by field2)

$result = ( $row['field3'] - ($row['field1'] / $row['field2']) );
echo "<td>".$result."</td>";