I am trying to sort a table - lectures - as follows:
try
{
$sql = 'SELECT * FROM lectures ORDER BY (intval(lecturedate)) ';
exec($sql);
}
catch (PDOException $e)
{
$error = 'Error sorting dates: ' . $e->getMessage();
include 'error.html.php';
exit();
}
However, although it doesn’t throw an error, it doesn’t actually seem to execute - there is no change in the table.
Can anyone help?
The exec function is used to evaluate a command at the system level ie. terminal. What you’re doing makes no sense in that regards. You need to use a MySQL adapter to issue a query to MySQL and return a result set. Preferably you would use PDO to connect to the database than execute a query.
Thanks oddz
I’ve tried this:
try
{
$sql = ‘SELECT * FROM lectures ORDER BY lecturedate’;
$pdo->exec($sql);
}
catch (PDOException $e)
{
$error = 'Error sorting lecturedate ' . $e->getMessage();
include 'error.html.php';
exit();
}
…and it still doesn’t work. Where $pdo is given by:
$pdo = new PDO(‘mysql:host = localhost; dbname=ijdb’, ‘username’, ‘password’);
What can I do now?
I’m not familiar with the $pdo->exec method.
Got a link to any documentation or the code for it?
exec — Execute an external program
string exec ( string $command [, array &$output [, int &$return_var ]] )
exec() executes the given command.
<?php // outputs the username that owns the running php/httpd process // (on a system with the "whoami" executable in the path) echo exec('whoami'); ?>
Posted edited by cpradio to remove fake sig
I’m using XAMPP as a local host with Apache and MySQL; could this be the problem? Everything works OK using the MySQL control panel so could there be a link missing or incomplete?
Thanks,
Maybe the ORDER BY is causing a problem?
Since it isn’t important you might as well remove it.
And there is no need to SELECT everything, the one field would be enough.
Try this simplified query
SELECT lecturedate FROM lectures
Thanks Mittineague, but that would not give me what I am looking for. I am trying to sort the records in the lecturedate column; “SELECT lecturedate FROM lectures” will simply give me a list of lecturedate records.
Then, from the documentation you posted a link to, you do not want to use exec
For a SELECT statement that you only need to issue once during your program, consider issuing PDO::query(). For a statement that you need to issue multiple times, prepare a PDOStatement object with PDO::prepare() and issue the statement with PDOStatement::execute().
Note: PDO in its default settings ignores SQL errors from the DB. you have to set it to error/exception mode to get a feedback at all.
$pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
According to that link you posted though, exec() doesn’t return the records anyway (“PDO::exec() does not return results from a SELECT statement.” it says), you need to use query() or prepare() and execute() for that. exec() would be used for something like updating or deleting a value, not extracting data. I think @mittineague was suggesting dropping the ORDER BY clause to see if that was causing the problem.
Can’t help but wonder if @tholland is literally trying to sort the database table as opposed to retrieving an array of sorted records.
which makes no sense whatever as databases use whatever internal order they like that is completely independent of the order things gat retrieved in.
Where in your script do you connect to the database?
Here is the code I’m running. I know from the output that it is working OK (htmlout() is a helper function to safely print to monitor). What I had hoped was that the re-ordered data would update the lectures table in the database, which is accessed via $pdo. However, this is not happening.
try
{
$sql = 'SELECT id, lecturedate, title, sub_heading, description FROM lectures ORDER BY lectures.lecturedate ';
$s = $pdo->prepare($sql);
$s->execute();
}
catch (PDOException $e)
{
$error = 'Error sorting lecturedate ' . $e->getMessage();
include 'error.html.php';
exit();
}
while ($row = $s->fetch())
{
$id = $row['id'];
$lecturedates = $row['lecturedate'];
$title = $row['title'];
$sub_heading = $row['sub_heading'];
$description = $row['description'];
?>
<p>
<?php htmlout($lecturedates);
htmlout($id);
$sql = 'UPDATE lectures SET lecturedate = :lecturedates, title = :title, sub_heading = :sub_heading, description = :description WHERE id = :id';
$result = $pdo->prepare($sql);
$result->bindvalue(':id', $id);
$result->bindvalue(':lecturedates', $lecturedates);
$result->bindvalue(':title', $title);
$result->bindvalue(':sub_heading', $sub_heading);
$result->bindvalue(':description', $description);
$result->execute();
?>
</p>
<?php
}
?>
Thanks droopsnoot
I had seen this in the documentation and tried it, but with no success
Yes, I didn’t explain that very clearly. I have a table of data and am trying to rearrange its rows in order of dates (lecturedate).
Unless I’m missing something it looks like you’re SELECTing data and then UPDATEing with the same exact data 
As felgall pointed out
Is not your query returning results as specified in the ORDER BY ?
Not going to happen. Basic misunderstanding of sql. There is no “order” in the database. You can only specify an order when you fetch rows.
I think this explains why so much of this thread seems to consist of people talking past each other.
Read through a couple of sql tutorials to understand the basics.