Problem with ORDER BY

anon80411031 · February 7, 2011, 8:55am

Hi! I have a database in PhpMyAdmin, linked to a php page where you can see all the information stored in the database inside a table. The problem is that the ORDER BY commands don’t work as I would like.

For example, this query

SELECT * FROM table WHERE conditions ORDER BY date

doesn’t order the entries in the right date order (date is the name of a column of the database). As a result I get something like this instead:

2010-10-10
2010-10-10
2010-10-25
2010-10-11
2010-10-08

I have the same problem with other ORDER BY commands (order by name in alphabetical order ecc.)

Am I doing something wrong?

r937 · February 7, 2011, 10:31am

can you do a SHOW CREATE TABLE please

ORDER BY date is exactly correct, so there’s gotta be a problem with the datatype of the column

anon80411031 · February 8, 2011, 4:53pm

This is the result of that command:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘’ at line 1

jurn · February 8, 2011, 5:28pm

what is the name of your table? If it is ‘magazines’ then try this command instead:

SHOW CREATE TABLE magazines;

anon80411031 · February 8, 2011, 6:42pm

Here you go:


CREATE TABLE `appuntamenti` (
 `nome` varchar(255) NOT NULL,
 `tipo` varchar(12) NOT NULL,
 `indirizzo` varchar(255) NOT NULL,
 `cf` varchar(20) NOT NULL,
 `piva` int(20) NOT NULL,
 `referente` varchar(255) NOT NULL,
 `telefono` int(11) NOT NULL,
 `telefono2` int(11) NOT NULL,
 `referente2` varchar(255) NOT NULL,
 `telefono3` int(11) NOT NULL,
 `telefono4` int(11) NOT NULL,
 `via` varchar(255) NOT NULL,
 `num` int(4) NOT NULL,
 `comune` varchar(255) NOT NULL,
 `provincia` varchar(2) NOT NULL,
 `cap` int(5) NOT NULL,
 `regione` varchar(255) NOT NULL,
 `data` date NOT NULL,
 `datafissaggio` date NOT NULL,
 `organizzatore` varchar(255) NOT NULL,
 `relatore` varchar(255) NOT NULL,
 `relatoreinaffiancamento` varchar(255) NOT NULL,
 `omaggistica` text NOT NULL,
 `numomaggi` int(11) NOT NULL,
 `coppie` int(11) NOT NULL,
 `persone` int(2) NOT NULL,
 `contributoacoppia` decimal(10,2) NOT NULL,
 `contributototale` decimal(10,2) NOT NULL,
 `pagamento` varchar(255) NOT NULL,
 `postdatato` decimal(10,2) NOT NULL,
 `storno` decimal(10,2) NOT NULL,
 `iban` varchar(50) NOT NULL,
 `beneficiario` varchar(255) NOT NULL,
 `numordini` int(2) NOT NULL,
 `fatturato` decimal(10,2) NOT NULL,
 `affarilordo` decimal(10,2) NOT NULL,
 `imponibile` decimal(10,2) NOT NULL,
 `note` text NOT NULL,
 `privilegio` int(11) NOT NULL,
 `responsabile` varchar(255) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

r937 · February 8, 2011, 7:12pm

data date NOT NULL looks okay to me

can we see the query that isn’t sorting properly?

anon80411031 · February 8, 2011, 7:17pm

Sure:


SELECT * FROM appuntamenti WHERE privilegio <= $privilegioutente AND (organizzatore = '$value' OR relatore= '$value' OR relatoreinaffiancamento = '$value') ORDER BY data

SpacePhoenix · February 8, 2011, 7:20pm

You need either ASC or DESC in the ORDER BY clause

anon80411031 · February 8, 2011, 7:23pm

I’ve already tried doing that, it still doesn’t work

guelphdad · February 8, 2011, 7:57pm

Please provide insert statements for a number of rows from the table that can duplicate this problem. you don’t need to include all columns, but choose the relevant rows that show the problem.

Are the rows returned correctly in phpmyadmin but incorrectly in your php code? If it is correct in phpmyadmin then the problem is your php code.

anon80411031 · February 8, 2011, 8:40pm

Phpmyadmin return the rows in the correct order.

What do you mean by provide insert statements?

r937 · February 8, 2011, 11:49pm

then this is not a mysql problem

guelphdad · February 9, 2011, 2:49am

Then you should have stated this in your original post. It would help you get a solution faster.

When asked for insert statements they would look similar to:


INSERT INTO yourtablename VALUES (1,'something here','anotherfield');
INSERT INTO yourtablename VALUES (13,'larry,','curlymoe');

SpacePhoenix · February 9, 2011, 7:02am

When your trying the use of ASC or DESC in the ORDER BY clause, have you tried it in PHPMyAdmin or in your script or both? Possibly PHPMyAdmin is assuming that you want the ORDER BY to be ASC.

anon80411031 · February 9, 2011, 9:05am

This is good to know But how is it possible that the same query, run from Phpmyadmin and from my PHP code, returns different results?

If I had noticed it before I would have written it in my first post, but I just realized it after your question

When asked for insert statements they would look similar to:


INSERT INTO yourtablename VALUES (1,'something here','anotherfield');
INSERT INTO yourtablename VALUES (13,'larry,','curlymoe');

This is the section of the PHP code which handles the insert:


$sql="INSERT INTO appuntamenti (nome, tipo, indirizzo, cf, piva, referente, telefono, telefono2, referente2, telefono3, telefono4, via, num, comune, provincia, cap, regione, data, datafissaggio, organizzatore, relatore, relatoreinaffiancamento, omaggistica, numomaggi, coppie, persone, contributoacoppia, contributototale, pagamento, postdatato, storno, iban, beneficiario, numordini, note)
VALUES
('" . $_POST['nome'] . "','" . $_POST['tipo'] . "','" . $_POST['indirizzo'] . "','" . $_POST['cf'] . "','" . $_POST['piva'] . "',
'" . $_POST['referente'] . "',
'" . $_POST['telefono'] . "',
'" . $_POST['telefono2'] . "',
'" . $_POST['referente2'] . "',
'" . $_POST['telefono3'] . "',
'" . $_POST['telefono4'] . "',
'" . $_POST['via'] . "','" . $_POST['num'] . "','" . $_POST['comune'] . "','" . $_POST['provincia'] . "','" . $_POST['cap'] . "',
'" . $_POST['regione'] . "','" . $_POST['data'] . "',
'" . $_POST['datafissaggio'] . "',
'" . $_POST['organizzatore'] . "',
'" . $_POST['relatore'] . "','" . $_POST['relatoreinaffiancamento'] . "',
'" . $_POST['omaggistica'] . "',
'" . $_POST['numomaggi'] . "',
'" . $_POST['coppie'] . "',
'" . $_POST['persone'] . "',
'" . $_POST['contributoacoppia'] . "',
'" . $_POST['contributototale'] . "',
'" . $_POST['pagamento'] . "',
'" . $_POST['postdatato'] . "',
'" . $_POST['storno'] . "',
'" . $_POST['iban'] . "',
'" . $_POST['beneficiario'] . "',
'" . $_POST['numordini'] . "',
'" . $_POST['note'] . "')";

I’ve tried it in both. Phpmyadmin returns the results in the correct order (both if I use ASC or DESC). My PHP code returns wrong results in both cases.

guelphdad · February 9, 2011, 4:00pm

What you have posted above is not what we are looking for. When you are asked for a set of insert statements it is so we can easily recreate a table(s) to test and come up with a solution. In this case we don’t need the insert statements. You are telling us your query comes up with the correct order in phpmyadmin. That means the problem is in the front end code in your php application that is at fault with returning your rows.

Can you show us the code that fetches and outputs the result and maybe we can determine from that what the problem is? Most likely this thread will move to php forum so you can get assistance with it from that point if the solution isn’t obvious from what you post.

anon80411031 · February 9, 2011, 6:20pm

Sorry, I didn’t understand. Here are some insert statements.


INSERT INTO appuntamenti VALUES ('restaurant1','address1','phonenumber1','2010-10-10');
INSERT INTO appuntamenti VALUES ('restaurant2','address2','phonenumber2','2010-10-10');
INSERT INTO appuntamenti VALUES ('restaurant3','address3','phonenumber3','2010-10-11');
INSERT INTO appuntamenti VALUES ('restaurant4','address4','phonenumber4','2010-11-09');
INSERT INTO appuntamenti VALUES ('restaurant5','address5','phonenumber5','2011-10-10');

These are some of the rows that I have right now in my test page.

And here’s the code for the SELECT and the output:


		<?php
			echo '<table id="incontri">
			<thead>
			<tr>
			<th></th>
			<th></th>
			<th>Nome</th>
			<th>Tipo</th>
			<th>Indirizzo</th>
			<th>CF</th>
			<th>P. IVA</th>
			<th>Referente</th>
			<th>Telefono</th>
			<th>Telefono 2</th>
			<th>Referente 2</th>
			<th>Telefono</th>
			<th>Telefono 2</th>
			<th>Via</th>
			<th>Num</th>
			<th>Comune</th>
			<th>Provincia</th>
			<th>CAP</th>
			<th>Regione</th>
			<th>Data</th>
			<th>Data fissaggio</th>
			<th>Organizzatore</th>
			<th>Relatore</th>
			<th>Affiancamento</th>
			<th>Omaggistica</th>
			<th>Omaggi consegnati</th>
			<th>Coppie</th>
			<th>Persone</th>
			<th>Contributo a coppia</th>
			<th>Contributo totale</th>
			<th>Tipo di pagamento</th>
			<th>Di cui postdatato (se assegno)</th>
			<th>Di cui storno (se assegno)</th>
			<th>IBAN (se bonifico)</th>
			<th>Beneficiario</th>
			<th>Num ordini</th>
			<th>Fatturato</th>
			<th>Giro d\\'affari lordo</th>
			<th>Imponibile provvigionale</th>
			<th>Note</th>
			</thead>
			</tr>
			<tbody>';

			foreach ($collaboratori as $value) {
		
				$sql2 = "SELECT * FROM appuntamenti WHERE privilegio <= $privilegioutente AND (organizzatore = '$value' OR relatore= '$value' OR relatoreinaffiancamento = '$value') ORDER BY data DESC";
# La parte WHERE privilegio <= $privilegioutente può essere inutile ora
				$result2 = mysqli_query($conn,$sql2);

				while ($row = mysqli_fetch_array($result2)) {
					echo '<tr>';
					echo '<td><a href="modifica.php'.'?'.'nome'.'='.$row['nome'].'&'.'data'.'='.$row['data'].'">Edit</a></td>';
					echo '<td><a href="accordo.php'.'?'.'nome'.'='.$row['nome'].'&'.'data'.'='.$row['data'].'">Visualizza</a></td>';
					echo '<td>' . $row['nome'] . '</td>';
					echo '<td>' . $row['tipo'] . '</td>';
					echo '<td>' . $row['indirizzo'] . '</td>';
					echo '<td>' . $row['cf'] . '</td>';
					echo '<td>' . $row['piva'] . '</td>';
					echo '<td>' . $row['referente'] . '</td>';
					echo '<td>' . $row['telefono'] . '</td>';
					echo '<td>' . $row['telefono2'] . '</td>';
					echo '<td>' . $row['referente2'] . '</td>';
					echo '<td>' . $row['telefono3'] . '</td>';
					echo '<td>' . $row['telefono4'] . '</td>';
					echo '<td>' . $row['via'] . '</td>';
					echo '<td>' . $row['num'] . '</td>';
					echo '<td>' . $row['comune'] . '</td>';
					echo '<td>' . $row['provincia'] . '</td>';
					echo '<td>' . $row['cap'] . '</td>';
					echo '<td>' . $row['regione'] . '</td>';
					echo '<td>' . $row['data'] . '</td>';
					echo '<td>' . $row['datafissaggio'] . '</td>';
					echo '<td>' . $row['organizzatore'] . '</td>';
					echo '<td>' . $row['relatore'] . '</td>';
					echo '<td>' . $row['relatoreinaffiancamento'] . '</td>';
					echo '<td>' . $row['omaggistica'] . '</td>';
					echo '<td>' . $row['numomaggi'] . '</td>';
					echo '<td>' . $row['coppie'] . '</td>';
					echo '<td>' . $row['persone'] . '</td>';
					echo '<td>' . $row['contributoacoppia'] . '</td>';
					echo '<td>' . $row['contributototale'] . '</td>';
					echo '<td>' . $row['pagamento'] . '</td>';
					echo '<td>' . $row['postdatato'] . '</td>';
					echo '<td>' . $row['storno'] . '</td>';
					echo '<td>' . $row['iban'] . '</td>';
					echo '<td>' . $row['beneficiario'] . '</td>';
					echo '<td>' . $row['numordini'] . '</td>';
					echo '<td>' . $row['fatturato'] . '</td>';
					echo '<td>' . $row['affarilordo'] . '</td>';
					echo '<td>' . $row['imponibile'] . '</td>';
					echo '<td>' . $row['note'] . '</td>';
					echo '</tr>';
				}
			}

			echo '</tbody>';
			echo '</table>';
		?>

I am still not sure if this is what you asked for, but I hope we are getting closer

anon80411031 · February 12, 2011, 1:24pm

Can you please move this to the PHP section?

SpacePhoenix · February 12, 2011, 11:36pm

[COLOR=DarkGreen]Thread moved to PHP forum.

D3V4, for future reference, if a thread needs moving, click on the report post icon and say which forum a thread needs moving to.

Thanks[/COLOR]

anon80411031 · February 13, 2011, 2:41pm

Ok, thanks you

Topic		Replies	Views
SQL ORDER BY not working Databases	6	17574	July 17, 2010
Order by syntax Databases	4	524	April 21, 2010
Why is my ORDER BY query not working right with multiple columns? PHP	8	4103	October 10, 2019
Select with conditional order Databases	17	970	February 25, 2011
Basic question - order by date Databases	1	374	February 25, 2010

Problem with ORDER BY

Related topics