In PhpMyAdmin Insert the following code – substitute your appropriate fields GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON [Database_Name].* TO [Username]@localhost IDENTIFIED BY ”[password]”; GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, ALTER, INDEX, DROP ON [Database_Name].* TO [Username]@’%’ IDENTIFIED BY ”[password]”; Flush privileges; To Allow the user access to phpMyAdmin just make sure [...]
No one wants to be left to without a database backup in their time of need. I looked at a bunch of mysql backup scripts and had a hard time getting most to work. I found a nice and simple one that looks to work good from Island Linux. Well, as an extension to this [...]
To change the value of the increment number you can use the following: To start your records at 100: ALTER TABLE tbl_name AUTO_INCREMENT = 100 To start your records at 1000: ALTER TABLE tbl_name AUTO_INCREMENT = 1000
To select a lowercase variation of a particular field use the following query SELECT * FROM table WHERE field=’$var’ OR lower(field)=’$var’
This was a challenge to figure out. This query deletes all rows that are more than one day old and has a status of blank or null. DELETE FROM table WHERE (($field_date_added < now() – ”1 days”::interval) AND ($field_status=”” OR $field_status IS NULL))
In some cases you will want to change the last value for a sequence. You can do this by updating the current value of the sequence. SELECT setval(‘table_id_seq’, 123)
Here is the function: <?php function mysql_date($timestamp, $date_format=”") { //default date format if ($date_format == “”) { $date_format = “m-d-Y H:i:s”; } //break up mysql timestamp $year=substr($timestamp, 0, 4); $month=substr($timestamp, 4, 2); $day=substr($timestamp, 6, 2); $hour=substr($timestamp, 8, 2); $minute=substr($timestamp, 10, 2); $second=substr($timestamp, 12, 2); //build new timestamp $final_timestamp = mktime($hour, $minute, $second, $month, $day, $year); [...]
Log on to mysql command line interface >mysql db_name -OR- >mysql –user=user_name -p -O max_allowed_packet=8M db_name Tell mysql what file to parse >source filename
To get a portion of a field use the mysql substr command SELECT SUBSTR(field_name, start_position, length) FROM table_name; fieldname = the field that you want a piece of startposition = the start position of the piece(1 is beginning) length = how many characters in the piece SELECT SUBSTR(test, 1, 5) FROM country;
To check if a field is NULL use: SELECT * FROM table_name WHERE field_name IS NULL To check if a field is not NULL use: SELECT * FROM table_name WHERE field_name IS NOT NULL