[[SOLVED] SQL Query (convert dates)]

Thanks, you saved me like a million hours work

[[SOLVED] SQL Query (convert dates)]

Hi all

 

I have a database of 5,000 records and in each record is a field called 'dob' which has dates in it, the dates are stored as DD/MM/YYYY and I need to run a query on the database to convert all date records stored in 'dob' field to YYYY-MM-DD.

 

Can anyone help?

 

Thanks

 

Ed

[IF EXISTS - error]

im trying to see if a record exists and if it does to update the information and if it doesnt exist then it would insert the data

[IF EXISTS - error]

Hi all

 

I am trying to run the following query

 

IF EXISTS(SELECT * FROM `md` WHERE `rn` = 'DD') UPDATE `md` SET `rn`='CC' ELSE INSERT `md` SET `rn`='BB';

 

But I get a

 

#1064 - You have an error in your SQL syntax;

 

Does anyone know why?

 

Thanks

 

Dave

[Updating database]

will do, thanks

[Updating database]

Hi all

 

I have a script which uploads a CSV file and then updates any information contained in a SQL database, so if there is a new address for a user in the CSV file, then it updates the SQL database with the new information.

 

The code I use is

 

		if (isset($_FILES['documentfile']['size']) && $_FILES['documentfile']['size'] > 100)
	{
		$upfile = ''.$random."_".$_FILES['documentfile']['name'];

		if (!copy($_FILES['documentfile']['tmp_name'], $upfile))
		{
			print "Could not move file into upload directory";
			exit;
		}


$link = mysql_connect("localhost","***","***");
$selection = mysql_select_db("md_update", $link);

$filename = $upfile;

$id = fopen($filename, "r");
while ($data = fgetcsv($id, filesize($filename)))
$table[] = $data;
fclose($id);

foreach($table as $row)
{

$sql ="UPDATE md_update SET id='".$row['0']."',name='".$row['1']."',member_no='".$row

['2']."',tradingname='".$row['3']."',street='".$row['4']."',town='".$row['5']."',county='".$row

['6']."',post_code='".$row['7']."',phone='".$row['8']."',board_area='".$row['9']."',username='".$row

['10']."',password='".$row['11']."',validity='".$row['12']."',type_desc='".$row['13']."' WHERE id='".$row['0']."'";
@mysql_query($sql, $connection) or die(mysql_error());

}


unlink($upfile);			

}

}

 

Where abouts would I put an "else" statement? So if there is any new data in the CSV file, then it will "INSERT" the new data.

 

Any help would be great

 

Thanks in advance

 

Ed

[mysql_result error]

thanks, works fine now

[mysql_result error]

Hi all

 

I have the following code

 

<?php
$db_name ="***";
$server = "localhost";
$dbusername = "***";
$dbpassword = "***";

$connection = mysql_connect($server, $dbusername, $dbpassword) or die(mysql_error());
$db = mysql_select_db($db_name,$connection)or die(mysql_error());

$sql = "SELECT * FROM countries ORDER BY description ASC";
$query = @mysql_query($sql,$connection) or die(mysql_error());

while ($row = mysql_fetch_array($query))
{
$total = "SELECT COUNT(*) as Num FROM users WHERE `email` NOT LIKE '%@googlemail.com%' AND country_code = ".$row['code']."";
$results = mysql_result(mysql_query($total),0); 
?>
<?php print $row['description']; ?><br>
<?php print $results; ?><br><br>
<?php
}
?>

 

But when I run the page, I get the error

 

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/public_html/test.php

 

Does anyone know why?

 

I've checked and checked again, but cannot seem to find a answer

 

Any help would be great

 

Thanks

 

Dave

[[SOLVED] QUERY HELP]

Thanks, saved my life

[[SOLVED] QUERY HELP]

Anyone? Sorry its driving me crazzy.

 

Still trying though

[[SOLVED] QUERY HELP]

If possible, if not, whatever way is best

[[SOLVED] QUERY HELP]

Sorry, type is set to DATE for both START_DATE and END_DATE, both set to 0000-00-00

[[SOLVED] QUERY HELP]

Yep, the type is set to DATE in the format 0000-00-00

[[SOLVED] QUERY HELP]

Hi

 

I have a further question to my last post, but its slightly different.

 

I have a table called "events" with a "start_date" and "end_date", the dates are stored as YYYY-MM-DD

 

an example table would be

 

ID  START_DATE    END_DATE

1    2007-03-23    2007-03-28

2    2007-04-02    2007-04-02

 

I want to run a query on this table and return all rows that have todays date and the next 6 days (so 1 week) within the START_DATE and END_DATE fields.

 

So if todays date was 2007-03-26, then it would return only row 1 from the above example table, and if todays date was the 2007-04-01 then it would return just row 2 as it has a START_DATE starting the next day.

 

Does that make sense?

 

I have tried to do this query with the BETWEEN function, but it doesnt seem to be returning the correct result and sometimes no result.

 

Can anyone help me?

 

Thanks in advance

 

Dave

[[SOLVED] ORDER BY - Question]

Thanks

[[SOLVED] ORDER BY - Question]

Hi all

 

I have a standard QUERY, on the QUERY I use a

 

ORDER BY id

 

to sort the data that is returned.

 

One of my fields in my table is a date field and it is held in the following format "YYYY-MM-DD".

 

Is it possible to do a ORDER BY date using the date format I use.?

 

Any help would be sweet

 

Thanks

 

Dave

[Removing Word Formatting]

Hi all

 

I've recently built a simple CMS system with a textarea that the user can use to bolden text etc.

 

If you type directly into the box and then apply styling such as "bold", it works fine. The problem is that when you paste text from Microsoft Word, it seems to take all the MS word formatting, which really screws the page up and changes all the styling.

 

Does anyone know of a script which can clean up the word formatting and remove all the tags word might have added and then insert the new cleaned up text?

 

Any help would be great

 

Thanks

 

Ed

[List all months since YYYY-MM-DD]

Thanks

[List all months since YYYY-MM-DD]

Can this be reversed to display the months in a reverse order? Newest at top

[List all months since YYYY-MM-DD]

Sweet

[List all months since YYYY-MM-DD]

Hi all

 

How can I print in a list format, all the dates since 2006-12-01 up to today, for for example

 

December 2006

Janurary 2007

February 2007

March 2007

April 2007

 

and then as we get into a new month, it would be added onto the bottom.

 

I'm sure ive asked this before but I cant seem to find the post or example I saved.

 

Any help would be spot on

 

Thanks

 

Dave

[PHP Screenshot Generator]

Thanks

 

If anyone else has anything, that would be great

[PHP Screenshot Generator]

Hi all

 

Does anyone know of any real-time screenshot generator? Where you can give the PHP file a URL and it will convert that URL page into a JPEG image with GD?

 

I have had a look around and only found a few but none seemed to do this job.

 

Anyone?

 

Thanks

 

Ed

[[SOLVED] Random Function]

Spot on mate, nice one

[[SOLVED] Random Function]

Hi

 

I have a function that creates a random string, the function looks like

 

<?php
  function randomkeys($length)
  {
   $pattern = "1234567890abcdefghijklmnopqrstuvwxyz";
   for($i=0;$i<$length;$i++)
   {
     $key .= $pattern{rand(0,35)};
   }
   return $key;
  }
?>

 

The function has worked fine until the server guys did some changes to the server, now when you include the function, you get the error

 

Notice: Undefined variable: key in C:\www\mysite\functions\rand.php on line 7

 

Does anyone know why this is happening?

 

Thanks

 

Ed