mysql-query

Changing the MySQL query delimiter through the C API

How can I change the MySQL query delimiter using the C API? I tried sending DELIMITER | as a query, complained about ..the right syntax to use near 'delimiter' at line 1.. Tried DELIMITER |; too, no luck. Tried DELIMITER |; SELECT 1|, no luck. :( The reason I ask is that I need to create a trigger through the C API such as the followin...

Creating a messaging "system" in DB (most especially MySQL)

Hi guys, Sorry, I have a concern: I have a table successully created in MySQL: CREATE TABLE IF NOT EXISTS MESSAGE ( MESSAGE_ID BIGINT NOT NULL AUTO_INCREMENT ,AUTHOR_ID VARCHAR(30) NOT NULL ,TITLE VARCHAR(100) NOT NULL ,MESSAGE VARCHAR(4095) NOT NULL ,UNREAD_FLAG ...

How do I add data to different tables but keep the same id?

What I am trying to do is to add data for a product into a database with MySQL 5.1 and PHP 5.2.12. I have a table for general fields: name, description, price, number of times purchased, and also I have a table for the images that are associated with that product. The goal is to have the same id for every image that corresponds to its...

How to write efficient MySQL query to delete specific rows depending on constraints

Hello all! I got a question regarding my MySQL-database and would like to get input on what would be most efficient. My problem is as follows, I'm developing premium functionality for my board game web site. One premium functionality would be that all the games a user has played would be stored "forever" (for the user to look up after...

mySQL "Rank in Highscore"-Query

Hi there coders around the world, I'm working on a project where users can do certain things and gain points for it. To simplify this question let's say we got 2 tables user and points. -- table user -- table points +---------------+ +-----------------------------+ | id | name | | id | points | user_id | +--------...

Selecting data from two mysql tables in one query

Hi everyone, I have two tables, landlords and properties. My properties table has; ID, Address, Postcode, lease and landlordID in it. The problem I face is: If I want to search for all the properties that have Mr.Spina as their landlord I need search the landlords database with the name "spina" to get his ID which is saved in the prope...

Query to Delete Posts Older than X Days in WordPress

I run a blog where the community can post time-sensitive community links (sports scores and such). After a certain time, those posts are no longer useful, so I want to delete them in batch via a MySQL query, but I don't know how. I imagine that getting rid of those posts entirely is more than just deleting from the wp_posts table, right?...

How to filter posts with multiple category names in WordPress?

I am trying to work around a plugin for Word Press called "Event Calendar 3". This plugin lets you create events and then feeds them into a SQL table in the Wordpress database. However, this plugin does not discriminate between different types of events, eg repeating events, one-off events. The beginning of my solution was to create a c...

creating multiple tables with single sql command

I searched for this here and on google and surprisingly couldn't find an answer. I tried to create syntax to submit to mysql that would create multiple tables with the same columns, but it returned an error. Can you point out what is wrong with my syntax, or if this is even possible? CREATE TABLE news, life ( id int PRIMARY KEY AUTO_...

Having trouble parsing a MySQL result in php

I'm trying to pull some results from a database but I guess I'm kind of brain dead today. $castquery = "SELECT * FROM cast " . "WHERE player_id = '" . $userid ."' "; $castresult = mysql_query($castquery) or die(mysql_error()); $castrow = mysql_fetch_array($castresult); ... foreach($castrow['cast_id'] as $caster) { echo "<p>"...

Where Clause Woes in Stored MySQL Query

What I'd like to do is execute a MySQL query containing a where clause ("result query") that is stored in a column in the database. This column, containing the query, is a result of another query ("original query"). The catches: The result query's where clause can contain a variable value (or two) I don't know what the result query ...

Select data in mysql where delimiters are two data alphanumeric data fields.

My Table structure have two Columns, One for start range and another for End of Range. Both are alphanumeric. I have to search a row according to user input, a Alphanumeric string. like : SELECT form table where range1 <= 'user_input' and range2 >= 'user_input'; But range1, range2 and user_input are alphanumeric values (Barcodes). ra...

Querying MySQL with PHP

What is wrong with this code: $q = query("select * from users where email = '$_POST['email']' and name = '$_POST['name']'"); Parse error: parse error, expecting T_STRING' orT_VARIABLE' or `T_NUM_STRING' in C:\wamp\www\conn\index.php on line 16 Thanks in advance. ...

mysql - a SELECT that returns the highest 'views' and a specific id's 'views'

Is it possible to write a single query that can return two rows, one specified in a WHERE clause and the other the highest in the table. For example. Table_1 -row_id -views -content Is there a query which can combine the following: SELECT views FROM Table_1 WHERE row_id = 10 SELECT MAX(views) FROM Table_1 or is two queries my onl...

PHP Class problem

I'm working with the following code, and when run I get nothing displayed on the screen. class ModelBase { public $db_server; public $db_user; public $db_password; static $db_conn; public static $DBSERVER="localhost"; public static $DBUSER="user"; public static $DBPASSWORD="password"; public static $...

MySql conversion problem : from UNIX_TIMESTAMP to INT(11)

Hello, Running the below select on a MySql table containing 1500000 rows will take approximately 5 mins 30 seconds. SELECT * FROM my_table WHERE timestamp BETWEEN UNIX_TIMESTAMP('2008-04-23 01:37:02') AND UNIX_TIMESTAMP('2008-04-23 01:37:03') [Executed: 25/01/10 5:32:47 EST PM ] [Execution: 231094/ms] Converting and replacing the...

Using LOAD DATA INFILE with arabic data

I am trying to import a .csv file into a table. I have figured out how to get the data inserted by using the following query: LOAD DATA INFILE 'examplesofdata.csv' INTO TABLE coins FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '\\' IGNORE 1 LINES; However for several of my fields I have Arabic content which gets entered as a s...

Selecting with subqueries in MySQL

I know this is not a good practice of creating a table, so please let those thing aside first. I have a table, let say table A and table B Table A : | ID | fk_tableB_ID | title ------------------------- | 10 | 1,2 | title 1 | 11 | 3 | title 2 Table B : | ID | category --------------- | 1 | cat1 | 2 | cat2 | 3 | ...

mysql never releases memory

I have a production server clocking about 4 million page views per month. The server has got 8GB of RAM and mysql acts as a database. I am facing problems in handling mysql to take this load. I need to restart mysql twice a day to handle this thing. The problem with mysql is that it starts with some particular occupation, the memory con...

Getting data in a certain order using mySql

I'm busy writing a PHP app that gets data from a database, but I need to get the data in a certain order. My Query looks as follows $sql = "select id,title,type from campaigns where id=$cid order by type"; Now my problem is these are the different types 'GC','MJ','MU','MS','MW','MX','GS' and I want MX to always be select last, thus s...