mysql

mysql :How to select two different column values from a single table using two different values of another table.

Hi all, My goal is to select two different values,say c & d from table2 using the fields a and b of table1 in a single query. Any help would be deeply appreciated. Thanks. ...

PHP - two multiple select dropdowns, passing user selections into a MySQL query

EDIT: The drop down menus have the following listed in them: Typing Course Daily Marketing Course When using the code below to add selected text form the dropdown into the MySQL statement, only the first word appears ie. 'Typing' and 'Daily', the code looks like this: SELECT * FROM `acme` WHERE `course` IN('Typing', 'Daily')AND `date...

how i can use like statement in c#

i am adding parameter by qry = qry.Replace("{criteria}", "info.abc LIKE '%?val%'"); command not worked if i removed ' ' from the command then it give a error how i can search the table in c# ...

DateTime.Now to mysql datetime

Hi, i got problem with a query, got something like this command.CommandText = "SELECT " + "COUNT(a.`id`) " + "FROM " + "`messageaccess` a " + "WHERE " + "a.`Users_LOGIN` = '" + Settings.UserLogin + "' " + "AND a.`Status` = '" + Enums.MessageStatus.New + "' " + "AND a.`FOLDER` = '" + Enums.M...

MySQL: One row or more?

I have any kind of content what has an ID now here I can specify multiple types for the content. The question is, should I use multiple rows to add multiple types or use the type field and put there the types separated with commas and parse them in PHP Multiple Rows `content_id` | `type` 1 | 1 1 | 2 1 ...

IF statement in Propel select criteria

Hi everyone, I am trying to incorporate an IF statement into my propel criteria. This is the how I build up my criteria: $c = is_object($c) ? $c : new Criteria(); $c->addSelectColumn("CONCAT(DAYOFYEAR(" . SomePeer::CREATED_AT . "), ' ', YEAR(" . SomePeer::CREATED_AT . ")) AS period"); $c->addSelectColumn("COUNT(" . SomePeer::ID . ") AS...

MySql versions compatibility issue

I have a MySql script which runs successfully on Mysql 5.0.67. On Mysql 5.1.36 it simply gives a syntax error. I'm not sure since what version of MySql the script syntax problem started. Does anyone know of a compatibility issue from this older version to the later versions? Thank you ...

What's the best way of backing up a rails app data?

I need to make a backup system for my rails app but this has to be a little special: It doesn't have to back up all the database info and files in a single file or folder but it has to back up the database info and attachment files per user. I mean, every one of this backups should be able to regenerate all the information and files for ...

How to Pass Variable into a MySQL Stored Procedure from PHP

I have the following stored procedure: proc_main:begin declare done tinyint unsigned default 0; declare dpth smallint unsigned default 0; create temporary table hier( AGTREFERRER int unsigned, AGTNO int unsigned, depth smallint unsigned default 0 )engine = memory; insert into hier values (p_agent_id, p_agent_id, dpth); /* http...

Database design help needed

I am a large social networking site, say mynetwork.com. It will have a lot of features like albums, forums, jobs board and so on (Php + MySQL + Yii) Now, the users of this site will be able to create networks for their own community which can be accessed by community-1.mynetwork.com or community-2.mynetwork.com etc. So, I am not sur...

Returning rows based on sum of column

I'm trying to retrieve a subset of data based on the sum of a column. The goal is a script to release backordered items. Say we have 100 BO products and get 50 in stock. I'd like to update the oldest orders where the sum of order qty < 50. So something like: Sample Schema: Orders Table: order_id, order_date order_products Table: ...

Is there a better index to speed up this query?

The following query is using temporary and filesort. I'd like to avoid that if possible. SELECT lib_name, description, count(seq_id), floor(avg(size)) FROM libraries l JOIN sequence s ON (l.lib_id=s.lib_id) WHERE s.is_contig=0 and foreign_seqs=0 GROUP BY lib_name; The EXPLAIN says: id,select_type,table,type,possible_keys,key,key_len...

What is a better method to periodically backup mysql DB?

Hello all, I have designed a website that stores the client's information in MySQL through PHP. The whole website will be moved to a hosting company. Here is the question: What is a better strategy to periodically backup this critical DB data? Which tool I have to use in order to do so? Best wishes ...

Database Toolbox (sql-matlab) randomly returning different values for only windows 7 computer

Hey, So I am trying to use the database toolbox in matlab to query a database at my research lab, and on one specific computer, the results it returns from the same query changes each time you run the SAME query command. connPV = database('dbname','username','password'); Images = fetch(handles.connPV,['SELECT i.id, i.image_type_id, i...

MYSQL and REGEX compare

Hi Guys, I have some problem with REGEXP and MYSQL. How I cant ignore the accent on my string to compare with the regexp value? SELECT ('estágio') REGEXP '(estagio)') AS dados I cant remove the accent from the database. I tried to convert to _bin or utf8_unicode_ci , but with no sucess. Somebody has a tip for do that? tks ! ...

Calling a web service in an atomic way?

I have a MySQL database with product prices and from now on I want to synchronize another database with these values, but this other database is not in my server, and I can only update it through web services... Is there a way to make these two operations (updating my database and calling the web service for the other database to be upd...

What's the best and easiest way to Populate a dropdown based on another dropdown.

Hi all, Very simply, I have one dropdown menu dynamically populated with data: SQL Code $querycourse = "SELECT course, COUNT(course) AS count FROM acme WHERE course IS NOT NULL GROUP BY course "; $procc = mysqli_prepare($link, $querycourse); $queryc = mysqli_query($link, $querycourse) or die(mysqli_error($link)); PHP Code echo "<s...

Where can I find the MySQL log file in XAMPP

Hello all, I use PHP to access MySQL in XAMPP. My question is where I can find the MySQL log file if there is a DB error. Also, can I change the default location/name of that log file? Thank you ///// Based on the coments ////// mysql> show variables like '%log_file%'; +---------------------------+-----------------------------------...

representing login in ER diagram

Hi Guys, I'm trying to develop a system, and doing the ER diagram now, i have two questions I'm always thinking about the process and trying to do the ER, now i'm so confused i can't really think and do it, even though i have a knowledge in databases, how to over come this? how to represent a login in the ER? please elaborate guys? ...

Would you consider this a natural primary key?

Hi everyone. I'm writting a program with users who are identified by an id called RFC. In Mexico, this RFC is a 13 characters string used to identify tax payers in our country. No one, in the whole country, has the same RFC so I thought this would be a perfect natural primary key. This RFC would serve as a foreign key in other tables. T...