mysql

Working with models which don't have a single foreign key in CakePHP

I'm working on an application which has data imported from a foreign (and wholly unchangeable) source. I'll simplify some of the tables to explain the problem. Below are the tables with their primary keys. invoices (doc_number, date_printed, batch_number) headers (doc_number, date_printed, batch_number) deliveries (doc_number...

mysql insert multiple data into a single column or multiple row

Hello everyone, just want to ask for an opinion regarding mysql. which one is the better solution? case1: store in 1 row:- product_id:1 attribute_id:1,2,3 when I retreive out the data, I split the string by ',' I saw some database, the store the data in this way, the record is a product, the column is stored product attribute: a:3...

first data is missing in while loop

Hi i am trying to get data from db, but in while loop, first data is coming blank, let me show you <?php $query = "SELECT * FROM `artists` WHERE label_id = '$id' ORDER BY name"; $result = mysql_query($query); while ($info = mysql_fetch_array($result)) { ?> <option value="<?php echo $info['ID']; ?>"><?php echo $info['name']; ?...

MySQL select sum with group by gives integer results

I have 2 tables: CREATE TABLE `product_det` ( `id` bigint(12) unsigned NOT NULL AUTO_INCREMENT, `prod_name` varchar(64) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1; INSERT INTO `product_det` (`id`,`prod_name`) VALUES (1,'Pepper'), (2,'Salt'), (3,'Sugar'); CREATE TABLE `product_oper` ( `id` bigint(12...

C# connecting to MySQL -get DB user rights?

Hi, I am using System.data.odbc to communicate with my MySQL db. When logged, I would need to know whather the user is admin or not, just to know the rights. Is there any way how to retrieve rights of currently logged user? Thank you! ...

MySql Stored Procedure Select Table

Where I have more than one table in my database for use with (similar, but) different products. Is it possible to select a table to work with based on a parameter (example below)? (This would save me having multiple similar copies of the same procedure). Cheers. DELIMITER $$ DROP PROCEDURE IF EXISTS `dostuff` $$ CREATE PROCEDURE `...

Returning SQL Insert ID when using Jquery/AJAX to insert row into database

Im trying to add an element to a database and then return a new id for it based on the the sql_insert_id Its adding the element correctly and the console is returning the id i want to use but for some reason its not actually changing it on the element. Im just using an echo in my actions.php script to return the string. $(".add").clic...

MySQL MyISAM disk-bound scaling issue / drive cache

I have the following lookup-table: CREATE TABLE `widgetuser` ( `widgetuserid` char(40) NOT NULL, `userid` int(10) unsigned NOT NULL, `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', PRIMARY KEY (`widgetuserid`) ) ENGINE=MyISAM DEFAULT CHARSET=utf8 DELAY_KEY_WRITE=1; I have a widgetuser_tmp Table with the same structu...

How can I use PHP to find a row in a MySQL DB table and insert just one value?

For example, I know I can make a new row and enter values into all fields doing this: mysql_query('INSERT INTO table_name VALUES ( "id77" ,"some value" )'); Assuming the name of the first field ("id77") is "myID" and the name of the second field ("some value") is "foo"... How can I use PHP find the row with myID = id77 and change th...

A php function to insert or update mysql table on true/false

Im trying to write a function to check whether a user exists in a table, if so, update the row, if not, insert it. Here is my function: function UserExists($screenname){ $mysql = mysql_fetch_array ( mysql_query("SELECT * FROM `users` WHERE `screenname` = '$screenname' ")); if($mysql){ return TRUE; }else...

mysql compare enum

Here is a table i have CREATE TABLE `CUSTOMERSTATUSTYPES` ( `CustomerStatusId` int(1) unsigned NOT NULL auto_increment, `CustomerStatusName` enum('ACTIVE','SUSPEND','TERMINATE','CANCEL') default NULL, PRIMARY KEY (`CustomerStatusId`) ) ENGINE=MyISAM AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; When i write GROUP BY ... WHERE cst.C...

Stopping a delete on MySQL via Triggers

I'm trying to intercept any DELETE commands against a particular table. MySQL supports triggers but it doesn't seem to support a way to raise an error yet like SQL Server and other databases. Can I do this with just an empty Trigger definition? Something like: create trigger trListsDelete on lists instead of delete as begin /* Do n...

PHP short unique ID generation using auto_increment?

I would like to generate a short, unique ID without having to check for collisions. I currently do something like this, but the ID I currently generate is random and checking for collisions in a loop is annoying and will get expensive if the number of records grows significantly. Normally worrying about collisions isn't an issue, but t...

Whether to use "SET NAMES"

In reading "High performance MySQL" from O'Reilly I've stumbled upon the following Another common garbage query is SET NAMES UTF8, which is the wrong way to do things anyway (it does not change the client library's character set; it affects only the server). I'm a bit confused, because I used to put "SET NAMES utf8" on the ...

casting in MySQL

I have a varchar field that looks like (sadly I have no control over the data): Fri, 30 Oct 2009 06:30:00 EDT Is there a way to cast this into a timestamp format so that I can then sort based on it? ...

MySQL Syntax for setting Default Date

How can we set element to some default date in mysql ? In oracle we would do something like start_date DATE DEFAULT to__date('01011900','DDMMYYYY') mutation_date_time DATE DEFAULT SYSDATE CONSTRAINT entity_specification UNIQUE(external_name, start_date_time, end_date_time)) Also do we have any site or resource where we can get ...

Recommened SQL Priviledges for MySQL users for CMS'es

I have been experimenting with a lot of web development apps like Drupal, Moodle, Efront and Elgg lately. For better security, I run test servers on a XAMPP setup on a virtual XP machine inside Mac OS X 10.4, using Parallels. I think this should be pretty secure, right? When installing software like Elgg, it asks me to create a user in ...

MySQL "CREATE TABLE IF NOT EXISTS" -> Error 1050

Using the command: CREATE TABLE IF NOT EXISTS `test`.`t1` ( `col` VARCHAR(16) NOT NULL ) ENGINE=MEMORY; Running this twice in the MySQL Query Browser results in: Table 't1' already exists Error 1050 I would have thought that creating the table "IF NOT EXISTS" would not throw errors. Am I missing something or is this a bug? I am ...

mysql_escape_string problem with image url

I have a text-area where text and an image url is entered. Text is formatted like this: my best friend's email is ............ etc http://www.google.com/intl/en_ALL/images/logo.gif After the form is submitted, I use mysql_escape_string() to store the value in my database My problem is image the url has ch...

Is there a mysql function that will format a unix timestamp?

I have a unix timestamp that I would like to convert using mysql if possible. I'd like to have: Mon. May 21st 2009 I can of course do this with PHP but why if I can have the database do it. Does a function exist for this? Thanks. ...