mysql

Primary key versus key

When creating a mysql dump containing the structure of my database, one of the tables shows the following: CREATE TABLE `completedTransactions` ( `paymentId` int(10) unsigned NOT NULL, `timestamp` int(15) unsigned NOT NULL, `actionTaken` varchar(25) NOT NULL, `response` varchar(255) NOT NULL, `responseCode` int(5) NOT NULL, ...

Hex characters in regexp matching in mysql

I've spot very odd behavior of mysql. The select below returns 0: SELECT CONVERT('a' USING BINARY) REGEXP '[\x61]' However semantically identical select below returns 1: SELECT CONVERT('a' USING BINARY) REGEXP '[\x61-\x61]' Do you know what is happening here? I've tested that in mysql 5.0.0.3031 and 4.1.22 I need the hex character...

how can i set custom message to sql query for this script

got this: if (mysql_num_rows($ak) == 0) { $sonuc = mysql_query($sql) or die(mysql_error()); } elseif (mysql_num_rows($ak) == 1) { $sonuc = "you already added"; } this script works succesfully. but mysql_query($sql) echos "1" how can let it echo "successfully added." thanks... (im asking simple questions because im new to php)...

How can I check if a binary string is UTF-8 in mysql?

I've found a Perl regexp that can check if a string is UTF-8 (the regexp is from w3c site). $field =~ m/\A( [\x09\x0A\x0D\x20-\x7E] # ASCII | [\xC2-\xDF][\x80-\xBF] # non-overlong 2-byte | \xE0[\xA0-\xBF][\x80-\xBF] # excluding overlongs | [\xE1-\xEC\xEE\xEF][\x80-\xBF]{2} # straight 3-byt...

Getting hierarchy data from a self-referencing table

Let's say you have the following table: items(item_id, item_parent) ... and it is a self-referencing table as item_parent refers to item_id. What SQL query would you use to SELECT all items in the table along with their depth where the depth of an item is the sum of all parents and grand parents of that item. If the following is the co...

Does this MySQL Query have any hidden gotchas?

Hello, I hope this isn't a terribly obtuse question. I notice that MySQL will let me refer back to the "Mnth" field in my GROUP and ORDER. This is not the case in every db engine I've worked with. Can anyone confim that this is an OK approach? SELECT DATE_FORMAT(FROM_UNIXTIME(`swauditlogs`.`dateline`),'%Y-%m') AS Mnth, Count(`swauditlo...

Sometimes capitalized column name will be converted to lower case in MySQL windows ?

Like accountId -> accountid Has anyone met this kind of problem? It doesn't happen every day,though. ...

MySQL selecting and grouping rows if one after another

Hello, I'm developing an auctions application in which bids are held in a table with the following format: id | user_id | auction_id | placed_at I'd like to group the bids by user id and select their counts, but only if the user ids appear one after another. The bids are ordered by placed_at descending. Here's what I mean: 1 | 1 | 1...

MYSQL nested loop stored procedure issue

I am trying to do a simple stored procedure in mysql which has a nested loop. The idea is to check to see if the table has any values and if not then insert them. below is the code of the stored proc. I have tested all parts of the code and if i comment out the nested loop it will loop through all the values for the _my_curs_ fine. But w...

What's the difference between := and = in MySQL?

mysql> set @num := 1; Query OK, 0 rows affected (0.00 sec) mysql> set @num = 0; Query OK, 0 rows affected (0.00 sec) mysql> select @num; +------+ | @num | +------+ | 0 | +------+ 1 row in set (0.00 sec) Seems both works. ...

How to select N records for M group in MySQL?

This article elaborates how to select N records each group: http://www.xaprb.com/blog/2006/12/07/how-to-select-the-firstleastmax-row-per-group-in-sql/ The best solution for this kind of job is: set @num := 0, @type := ''; select type, variety, price, @num := if(@type = type, @num + 1, 1) as row_number, @type := type as du...

Help finishing MySQL Query?

I have two tables users - id - name - email users_groups - user_id - group_id There are a couple more fields but these are the ones I am trying to grab. I am trying to return 'id, name, email, group_id'. I think I have the first part of the query right, I just don't understand how do the WHERE statement. Could someone show me the way...

how to mysql_real_escape_string SQL Query?

theres single quotes using in $bio = "$user->description"; inserting DB like this; <? $sql = "insert into yyy (id, twitterid, twitterkullanici, tweetsayisi, takipettigi, takipeden, nerden, bio, profilresmi, ismi) values ('', '$id', '$twk', '$tws', '$tkpettigi', '$tkpeden', '$nerden', '$bio', '$img', '$isim')"; $ak = mysql_query("sel...

Composite key or New Unique key

I want to know which will be the best. I am having a table say A having col1(is a foreign key fk1),col2(is also a foreign keyfk2). Can I use PK as (col1,col2) or a newly generated PK. The queries will have fk1 in criteria and fk2 in joins. Kindly suggest me a best one. P.S I am using mysql ...

Calling MYSQL from inside an Innosetup project.

Hi I'm writing an installer for some software that uses MySQL. I'm trying to run a .sql script to set up the database on install. Alas I'm having big problems getting it to execute at the moment. The issue seems to arise from the fact that the moment you put a path to the .sql file inside the --execute="SOURCE " command everything f...

Dynamically create PHP object based on string

I would like to create an object in PHP based on a type defined by a string in a MySQL database. The database table has columns and sample data of: id || type || propertyVal  1 || foo    || lorum  2 || bar    || ipsum ...with PHP data types class ParentClass {...} class Foo extends ParentClass {private $id, $propertyVal; ...} class Ba...

One to many link table causing duplicate rows returned

Everything I've seen so far is about removing duplicate entries in a database automatically. I want to stress at the beginning of this that there is no duplicate data in the database. I'll also start with the fact that I'm very much still learning about RDBMS design, normalization, relationships, and, most of all, SQL! I have a table ...

[Mysql]: Inserting values into an intermediate table

I have recently added an intermediary table to link two tables. Tables: Purchase: Transaction_Id Purchase_Id Schedule: Show_Id Price Purchase_Schedule: PurchaseId(fk) Show_Id(fk) My problem is that purhcase_id is on auto_increment. So i'm not sure where to pull that ID from. I was going to do something like this... INSERT...

How do I pass more than one variable via URL in php?

Hello, I am trying to pass multiple variables in a URL in PHP to GET some info, but I don't think it's working. $allowedFunctions = array( 'returnAllProducts', 'refreshCurrentProduct' ); $IDNUM = $_GET[ 'idNum' ]; $functionName = $_GET[ 'func' ]; if( in_array( $functionName, $allowedFunctions ) && function_exists( $function...

MySQL: A foreign key referencing two parents

Have an interesting situation: I have a foreign key from a.name (child table) to b.name (parent table). b.name does not have a unique constraint on it, so it may contain duplicate values. If I put the name 'bob' in b.name twice, and then put it in a.name, I can no longer delete or update either instance of 'bob' in table b. In both cas...