mysql

SQL: Select the highest conditional sum on a table

I have an OrdersProducts tables with order_id, product_id, and quantity fields. I'd like to know the most popular products, so how can I sum the quantity field for each product_id separately and then order the sums? Any pointers would be appreciated. I'm using MySQL 5.3, thanks. ...

Problem accessing a MySQL database over ODBC

OdbcDataReader q = dbc.Query("SELECT * FROM `posts` WHERE `id`=" + id.ToString()); if (q.RecordsAffected < 1) { this.Exists = false; } else { this.Exists = true; this.Author = q.GetString(6); } The server returns No data exists for the row/column. My database table is structured like this (screencap from phpMyAdmin) By t...

Mysql Create Table with dynamic database name

Hi All, I am creating database at runtime and I want to create the tables in that database at the same time. can anyone give me any thought on how to do that? For Example - I have created one database named 'mydb' and now in the same proess i am trying to create the table i am using the mysql stored procedure for the same. my proc inp...

SQL: Insert multiple sets of values in one statement?

Is it possible to insert multiple sets of values to a SQLite table in one statement? I was trying: INSERT INTO the_table VALUES (1,2,'hi'),(2,0,'foo'); with the different ()s representing different insert sets, but I get an error. ...

Execute shell command from within MySQL client?

In oracle database, command "host" can be used to run bash command from database command window. Is there a equivalent command as "host" in mySql? ...

Mysql Prepare Statement error while executing

Hi All, I am trying to create a table though prepare statement but it is giving me syntax error. well if o try to execute the same statement individually then it work fine. here's my statement - SET @Stmt1 = Concat('DROP TABLE IF EXISTS ',DB,'.`county`;\n' 'CREATE TABLE IF NOT EXISTS ',DB,'.`County` ( `CountyID` INT UNSIGNED NOT NULL...

how to customize `show processlist` in mysql?

I want to order by Time,but seems no way to do that ? mysql> show processlist; +--------+-------------+--------------------+------+---------+--------+----------------------------------+------------------------------------------------------------------------------------------------------+ | Id | User | Host | db ...

High-traffic percentage-based set picks?

The setup: High traffic website and a list of image URLs that we want to display. We have one image spot, and each item in the set of image URLs has a target display percentage for the day. Example: Image1 - 10% Image2 - 30% Image3 - 60% Because the traffic amount can vary from day to day, I'm doing the percentages within blocks of 1...

How can I draw my database design?

I have mysql database and I want a software which can draw the database design for me? is there any way to do this? Thanks ...

mysqli->prepare function returns null

This is a terrible question because I don't have a simple way to reproduce it. However, I'm using the Zend Framework to connect to my MySQL database on OS X. Sometimes a call to the prepare function on a mysqli object returns null. The stated return values for the prepare function are false or a statement object. I can't figure out w...

PHP + MySQL Error

Hello, here's the code: if ($_POST) {$content = stripslashes($_POST['content']); $by = $_SESSION['exp_user']['username']; $dt = date("F j, Y, g:i a"); mysql_query("UPDATE tbl_intmsg SET time = ".$dt.", by = ".$by." AND content = ".$content."") or die(mysql_error()); For which I recieve error: You have an error in your SQL syntax; ch...

what's the equivalent version of MYISAM for show innodb status in mysql

by Show innodb status I can see information about deadlock and so on, but I don't know is there an equivalent version for MYISAM-engine tables? ...

How do you manually update a MySQL table's index?

I reset the previous index by accident, and now I need to manually update it so that index = number of rows in table. What is the sql command for this? ...

how to set timezone of mysql?

on one server,when I run: mysql> select now(); +---------------------+ | now() | +---------------------+ | 2009-05-30 16:54:29 | +---------------------+ 1 row in set (0.00 sec) on another server: mysql> select now(); +---------------------+ | now() | +---------------------+ | 2009-05-30 20:01:43 | +-----...

How do I formulate a query using the Symfony Plugin DbFinderPlugin to count grouped by columns?

I cannot figure out how to write the following query using the DbFinderPlugin 1.2.2 with Symfony and Propel: SELECT species, COUNT(*) FROM Bird GROUP BY species; Here is the DbFinderPlugin page I am rather new to the plugin, and I do love it so far, but this query has so far stumped me. ...

Tricky PHP/Mysql date calculation

I have a date such as 2009-06-30 (30th june, 2009). I want to calculate a date which appears 2 months, and 3 days before or after the first date. Or, 3 months, 6 days, before or after, etc. How can I do this? Is there an easy way using DATE_SUB() or DATE_ADD() ...

mysql query works manually but returns no results when running from code

I use a very simple query with "Like" to find product by its name SELECT p_pid, p_name, p_cat FROM products WHERE p_sid=346 AND p_name LIKE 'product name here in utf-8 encoding, can be various languages' LIMIT 1 When I run this query from code with valid product name, I get no results. If I copy the query (echoed by php to the browser...

mySQL field with a dashed line under it.?

can anyone please explane what does that dashed line stands for i am using phpmyadmin field are name type NULL default longtitude float No 0.001 latitude float No 0.001 thank you. ...

How stable is MySQL 5.4

I have just found out about MySql 5.4 – How stable is this version? Where can I find more information on this version? ...

Using MySQL Databases and PHP to Populate forms.

I am contemplating taking the next step with my PHP applications and making the option fields dynamic. That would open the doors for more automation. I have drop downs throughout my project, they are used to select a specific user and I update them manually when a new user is added (which is also a manual process). But if i take the fi...