Replace stuff in the select

Anon

Please help.

I have a database with dates that look like: 03/18/02

Now when I sort by date, it does not work.
Is there a way to sort the records by date 031802 and still allow the end user to see 03/18/02 ?

This just shows the date like 031802:

$result=mysql_query ("select * from table WHERE custid=$uid Order by $order $sort limit $offset,$limit");

if ($trn = mysql_fetch_array($result))

$date = ereg_replace ("[^0-9]","", $trn["date"]);

Anon

Ron,

Is your column type either Date or DateTime?

If it is then the sort should automatically come back in correct date order.

PHP has a function strftime that allows you to control the output format of your dates.
http://www.php.net/manual/en/function.strftime.php
Mysql has the function date_format which does the same sort of thing:

http://ftp.plig.org/pub/mysql/doc/D/a/Date_and_time_functions.html

Cheers

Justin

Anon

Thank You, I wish it were in formatted that way, then I would have no problem. I have 800,000 lines with the date input as a varchar exp. 03/18/02.

Thanks any way.

Anon

Hi, this is my script to update info in my db, it goes throught the motions and displays "Thank you! Information updated" but it does actually seem to have changed the info at all, I am rather new to this stuff and i cant work out what the matter is......if i could get some help it would be much appreciated..thanks

<body>

<?php

$db = mysql_connect("localhost", "root");

mysql_select_db("antiques",$db);

if (isset($id)) {

if (isset($submit)) {

$sql = "UPDATE general SET name='$name', description='$description', price='$price', image_name='$image_name', WHERE id=$id";

$result = mysql_query($sql);

echo "Thank you! Information updated.\n";

} else {

$sql = "SELECT * FROM general WHERE id=$id";

$result = mysql_query($sql);	

$myrow = mysql_fetch_array($result);

?>

<form method="post" action="<?php echo $PHP_SELF?>">

<table><tr>

<input type=hidden name="id" value="<?php echo $myrow["id"] ?>">


<tr><td>Name:</td><td><input type="Text" name="name" value="<?php echo $myrow["name"] ?>"></td></tr>

<tr><td>Description:</td><td><input type="Text" name="description" value="<?php echo $myrow["description"] ?>"></td></tr>

<tr><td>Price:</td><td><input type="Text" name="price" value="<?php echo $myrow["price"] ?>"></td></tr>

<tr><td>Image:</td><td><input type="Text" name="image_name" value="<?php echo $myrow["image_name"] ?>"></td></tr>

<tr><td><input type="Submit" name="submit" value="Enter information"></td></tr>

</table>

</form>

<?php

}

} else {

$result = mysql_query("SELECT * FROM general",$db);

while ($myrow = mysql_fetch_array($result)) {

printf("<a href=\"%s?id=%s\">%s %s</a><br>\n", $PHP_SELF, $myrow["id"], $myrow["name"], $myrow["description"]);

}

?>
</body>

jc94062

Try using explode or split with / as a delimiter.

mistcat

$sql = "UPDATE general SET name='$name', description='$description', price='$price', image_name='$image_name', WHERE id=$id";

I think the comma after '$image_name' is tripping you up. Try removing it. =)

Anon

Ron,

If possible it would be worth switching the data type over to a date field. The relative merits are:

Pros:
1. DBMS engine is doing the work in limiting your selects.
2. Correct indexing on the date field should produce much quicker query.
3. Your memory consumption is likely to be much lower as PHP doesn't need to build a large array and then sort it.

Cons:
1. It's a fairly substantial change.
2. Any pages that refer to this field will need to be checked.
3. You will need to stop any info going into the database in the period that you make these changes to ensure consistency.

FYI, I've just recreated your situation with a database that I have of 2.2 million entries. The timings are:

Create new dummy column with column type date ~1.5 mins
Update the 2.2 million rows ~ 4 - 5 mins.
Drop the old column ~3 mins
Rename the dummy column: ~2 mins.

You will need to set some time aside for analysing the table up to date. Also, if you have any indexes on your varchar field, it's worth droping them before you do this operation.

The update statement I used is :

update staff2 set date_2 = concat_ws('-',right(trim(date_1),2), mid(trim(date_1),4,2),left(trim(date_1),2) )

Date_1 is the varchar, date_2 the date field.

Hardware used:
AMD 800 Duron, 640Mg Ram, 10Gb disk partition, Mysql running in default setup.

All good food for thought.

CHeers

Justin