x404.co.uk
http://www.x404.co.uk/forum/

MySQL help
http://www.x404.co.uk/forum/viewtopic.php?f=4&t=12020		 Page 1 of 1
Author:  tombolt [ Mon Jan 10, 2011 1:43 pm ]
Post subject:  MySQL help
Hi all, I've got a database for my job list and it works a treat, however, I want to change the format of the date column. I currently have:

Code:
$mysqli = mysqli_connect("localhost", "root", "root", "Job_list");
      $sql = "SELECT * FROM Jobs ORDER BY Job_number DESC";
      $res = mysqli_query($mysqli, $sql);
      ?>

      <table class="joblist">
      <tr class="joblisttitle">
      <td><a href="order/ascjobnumb.php">Job number</a><br /><img src="pics/downarrer.gif" /></td><td><a href="order/ascclient.php">Client</a></td><td><b>Description</b></td><td><b>Date</b></td><td><a href="order/ascstatus.php">Status</a></td><td><b>Notes</b></td>
      </tr>

      <?php
      while ($line = mysqli_fetch_array ($res, MYSQLI_ASSOC)) {
      ?>
      
      <tr>
      <td id="jobnumber"><?php echo $line[Job_number];?></td>
      <td id="client"><?php echo $line[Client];?></td>
      <td id="description"><?php echo $line[Description];?></td>
      <td id="date"><?php echo $line[Date];?></td>
      <td id="status"><?php echo $line[Status];?></td>
      <td id="notes"><?php echo $line[Notes];?></td>
      <td><form action="editJob.php" method="POST"><input type="hidden" name="jobnumber" value="<?php echo $line[Job_number];?>" /><input type="hidden" name="status" value="<?php echo $line[Status];?>" /><input type="image" img src="pics/edit.gif" alt="Edit" /></td></form>
      </tr>

      <?php      
      }
      ?>
      
      </table>


I know I need to use the DATE_FORMAT() somehow as:

Code:
SELECT DATE_FORMAT('Date', '%d/%m/%Y')


The trouble is, I don't know where to put it. If I put it as part of the search query, it doesn't work, nor does it work if I do it separately.
Author:  tombolt [ Mon Jan 10, 2011 3:31 pm ]
Post subject:  Re: MySQL help
Well, I've done it in PHP by using the following:

Code:
      <?php
      while ($line = mysqli_fetch_array ($res, MYSQLI_ASSOC)) {
      $date = $line[Date];
      $date = strtotime($date);
      $date = date('d/m/Y', $date);
      ?>
      
      <tr>
      <td id="jobnumber"><?php echo $line[Job_number];?></td>
      <td id="client"><?php echo $line[Client];?></td>
      <td id="description"><?php echo $line[Description];?></td>
      <td id="date"><?php echo $date;?></td>
      <td id="status"><?php echo $line[Status];?></td>
      <td id="notes"><?php echo $line[Notes];?></td>
      <td><form action="editJob.php" method="POST"><input type="hidden" name="jobnumber" value="<?php echo $line[Job_number];?>" /><input type="hidden" name="status" value="<?php echo $line[Status];?>" /><input type="image" img src="pics/edit.gif" alt="Edit" /></td></form>
      </tr>

      <?php      
      }
      ?>
      
      </table>


I'd still like to know how to make MySql do the work if anyone knows how though.
Author:  tombolt [ Mon Jan 10, 2011 7:27 pm ]
Post subject:  Re: MySQL help
Had the whole thing working a treat on MAMP, so installed it on my ISP's server and it mostly works. However, I get the following error when trying to update a record:

Quote:
Could not update record: 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 'time.' WHERE Job_number = '1021'' at line 1


The code I'm using is:


Code:
$sql = "UPDATE Jobs SET Description = '$_POST[description]', Status = '$_POST[status]', Notes = '$_POST[notes]' WHERE Job_number = '$_POST[jobnumber]'";


I really can't see anything wrong in that and it worked fine on MAMP. MySql version 5.1 on my ISP, not sure what MAMP is on at the moment as I'm at home and it's at work.
Author:  forquare1 [ Mon Jan 10, 2011 10:02 pm ]
Post subject:  Re: MySQL help
The main problem I've had from jumping from MAMP to Apache is capitalisation issues, MAMP seems somewhat lax about capitalisation whereas Apache is really strict. Might be work checking variable names, etc.
Author:  tombolt [ Mon Jan 10, 2011 10:56 pm ]
Post subject:  MySQL help
I might just do that, I've already had on capitalisation issue, but that was just a simple URL.
Author:  tombolt [ Tue Jan 11, 2011 9:53 am ]
Post subject:  Re: MySQL help
Well, tried it at work this morning and seems to be running fine. Don't know what all that was about.
Author:  big_D [ Wed Jan 12, 2011 4:40 am ]
Post subject:  Re: MySQL help
The SELECT DATE_FORMAT('Date', '%d/%m/%Y') works as follows:

SELECT col1, col2, col3, DATE_FORMAT(col4, '%d/%m/%Y) as myDate FROM ...
Author:  tombolt [ Wed Jan 12, 2011 11:24 am ]
Post subject:  MySQL help
Aah, thanks Dave, so I can't do a select all, have to enter each column and specify which one to format. Thanks, makes sense.
Author:  big_D [ Thu Jan 13, 2011 4:30 am ]
Post subject:  Re: MySQL help
tombolt wrote:
Aah, thanks Dave, so I can't do a select all, have to enter each column and specify which one to format. Thanks, makes sense.

Yep. Although I think mysql drops the "as" (SQL Server and Oracle use AS, I believe, the small changes always confuse me and I haven't done any SQL for a while), so it would be SELECT FORMAT_DATE(...) myDate FROM ...
Author:  tombolt [ Thu Jan 13, 2011 1:12 pm ]
Post subject:  MySQL help
Cool thanks.
Page 1 of 1 All times are UTC
Powered by phpBB® Forum Software © phpBB Group
https://www.phpbb.com/