Mysql + Php Question

[omarsdali 0]

Hi,I am creating a website in php and using a mysql database to store the data. I want to store time data in the database. My question is what is the best datatype I should use and how do I read the time from the database and format(display) it using php ?

[sonesay 7]

The best datatype to use for each field would depend on what kind of data you intend to store. You will have to get fimilar with these datatypes before you begin to use it or you may end up with the wrong ones. I'm not sure what your asking on the last question. Do you want to know how to display the data in your database? You mention time in there do you mean time from the database? I'm not sure mysql stores time.

[jlhaslip 4]

SQL code for adding Table to the Database:

CREATE TABLE users (
user_id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT,
first_name VARCHAR(15) NOT NULL,
last_name VARCHAR(30) NOT NULL,
email VARCHAR(40) NOT NULL,
password CHAR(40) NOT NULL,
registration_date [b]DATETIME NOT NULL[/b], 
PRIMARY KEY (user_id)
);

#INSERT INTO users (first_name, last_name, email, password, registration_date) VALUES ('Larry', 'Ullman', 'phpmysql2@DMCInsights.com', SHA('password'), NOW());

php code for MySql:

$query = "SELECT CONCAT(last_name, ', ', first_name) AS name, [b]DATE_FORMAT(registration_date, '%M %d, %Y') AS dr[/b] FROM users ORDER BY registration_date ASC";

php code for printing the Results of the above query:

while ($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
	echo '<tr><td align="left">' . $row['name'] . '</td><td align="left">' . $row['dr'] . '</td></tr>
	';
}

So, the DB Table has a field that is "DATETIME", that is added to the DB using the NOW(), that uses "DATE_FORMAT(registration_date, '%M %d, %Y')" in the Query, and prints it out via php as an Associative Array string...

try it.

*code snippets from a Book on Php and MySql by author Larry Ullman, PHP and MySql For Dynamic Web Sites*

[shadowx 0]

jlhaslip covered good code there so take a look at that. Also google Tizag and maybe "php mysql tutorial" alongside it and you should find an excellent website for PHP and SQL tutorials which is where i learned all i know today.as for datatypes. Im a lazy person when it comes to DB's, unless you're going to be using functions like comparing two numbers to find the highest, or incrementing a number, or comparing dates you dont need to worry too much about data types (IMHO) So you can store a number in a TEXT field and it wont make any difference. Unless you ask the DB to compare the numbers to see which is higher etc.. then you have problems. Also with numbers in Ms access you couldnt start a number with 0 for some reason.. i dont know if this is true in MYSQl but if it is then numbers like phone numbers should always be in a TEXT field, otherwise you hit errors. As for text i just stick it into a TEXT field, it makes sense, i dont know the reason for all tghe other data types like blob and such.. they just seem useless when i have text, char and integer types. I guess its for validation but its better to validate with PHP and JS (or whatever language you're using) rather than passing it to the DB then handling the error it gives without showing the user a messy error message and then going back to the PHP page to get them to change it. Its just a waste of time and BW if you have a big site.

[gogoily 0]

I prefer "DateTime" typeIt stores data in form of "YYYY-MM-DD HH:mm:ss"

[iGuest 3]

Importing excel to mysql by using php coading without using csv it is possible?

Mysql + Php Question

 

Hi,

I am working in php from 3 months, I am new with php so I trying to convert excelsheet data into mysql without using csv. I am lot of trying but I cant solution. I tried to converting excel sheet into csv and then by using coding importing it through coding.

But in excel shee in address and products column there is lots of commas is assign.

So it can break from that and put it into next field into database. To avoid this situation is reticules because my excel sheet contain around 40,000 rows so I can not possible to modify csv file.

 

Please guide me and tell me direct importing method excel to mysql to solve my problem. It urgent.

 

Thanks & Regards

Chetan chopkar

Excel Informatics

 

 

-question by Chetan