mysql

Rails/Indexes performance Q - which is faster?

Table has columns: user_id and season_id Which is faster: #If I have a database index on user_id Table.find_all_by_user_id(some_id) or #If I have a database index on user_id and season_id Table.find_all_by_user_id_and_season_id(some_id, another_id) Is a multi-column index always faster? For example, is writing a multi-column index ...

Looking for a way to parse and insert XML string into MySQL using php

I am looking for a way to parse and insert following XML string data to MySQL tables using php.. please help <?xml version="1.0" encoding="UTF-8"?><brandModelListResponse> <brand ID="Nokia" Description="Nokia"> <model ID="N93i" OS="Symbian" OSVersion="SymS60V3" image="nokia-n93i.gif">N93i</model> <model ID="N95" OS="Symbia...

Mysql count id query

I'm using mysql table like this one: | userid | regdate | question | answer | ----------------------------------------------- 1 2010-10-14 question 1 answer1 2 2010-10-14 question 2 answer2 3 2010-10-15 question 3 answer3 4 2010-10-16 question 4 answer4 I want to count...

MySQL Count data for last 7 days

Hi, I have the following schema. Table votes +------------------+--------------+------+-----+---------------------+----------------+ | Field | Type | Null | Key | Default | Extra | +------------------+--------------+------+-----+---------------------+----------------+ | id | int(10)...

MySql: convert a float to decimal produce more decimal number then the stored in back.sql file

Hi at all, i want to understand this: i have a dump of a table (a sql script file) from a database that use float 9,2 as default type for numbers. In the backup file i have a value like '4172.08'. I restore this file in a new database and i convert the float to decimal 20,5. Now the value in the field is 4172.08008 ...where come from the...

How to insert data in PDo

I use PDO and i can't insert some data: use this code: $sql = 'INSERT INTO `releases` (id, artists, release, label, catalog, date, tracklist, type, status, vote, votes_count) '; $sql .= 'VALUES (:id, :artists, :release, :label, :catalog, :date, :tracklist, :type, :status, :vote, :votes_count)'; $query = $this->db->prepare($sql); $qu...

PHP Cron script efficiency using CURL to load files

Im pulling in search query results using CURL and then iterating through a database to load additional queries then storing the results back in the database. Im running into hassles with php maximum time and have tried setting the maximum time variable to a higher amount which i think isnt working on my host using this: ini_set('max_exe...

Separate cookies being stored for http://thewebsite.com and http://www.thewebsite.com?

My friend and I are creating a cookie-based login system using PHP and MySQL, in which when a user logs in, a cookie storing their login information is saved. The problem is, for each URL, http://thewebsite.com and http://www.thewebsite.com, separate cookies are stored. Is there a solution that would allow us to save the same cookie fo...

Order mysql fetched data by the same content of a field

Hello, i have a database with: city / age for example: Chicago 24 York 33 Chicago 54 London 12 York 21 London 1 How can i oreder thing like this? Chicago 24 Chicago 54 York 33 York 21 London 1 London 12 Basically order them by the name of the town. I use this for a php display records script. mysql_query("SELECT * FROM towns WHERE ......

Can i use LIKE twice?

i want to make a mysql selection but i need to select two types of data, one starting with 0256 and other with 0356. can i use: SELECT * FROM table WHERE tel LIKE '0256%' AND '0356%' ? thanks, Sebastian EDIT i have a problem with these queries in PHP. the query above, works fine in mysql, but in PHP i get results only for LIKE '0256%'...

Problem with NOT LIKE query in MySQL

hi, i want to make a selection that excludes the entries that start with 07, 0256 and 0356. this is what i tired: SELECT * FROM rapoarte WHERE nrtel NOT LIKE '07%' OR nrtel NOT LIKE '0256%' OR nrtel NOT LIKE '0356%' but it keeps selecting all the entries. ...

problem with MySQL queries in PHP.

I have the following queries that work perfectly in MySql: SELECT * FROM rapoarte WHERE nrtel LIKE '0256%' OR nrtel LIKE '0356% SELECT * FROM rapoarte WHERE nrtel NOT LIKE '07%' AND nrtel NOT LIKE '0256%' AND nrtel NOT LIKE '0356%' SELECT * FROM rapoarte WHERE nrtel LIKE '07%' in PHP they will result the following: results just for...

find value of row out of 3 columns in php and mysql

How can i find 3 in row id#1 with 4 columns in php id, Content_type1, Content_type2, Content_type3 1 6 3 9 ...

Specifying data directory for a specific database in mysql

I have a mysql database with 4 different databases. 1 of the databases has a lot of read/write activity that would benefit from being run on ramdisk. Due to the nature of the data layout and types of reads needed, memcached is not an effective option here. Therefore, I would like to run one of the databases in a ramdisk (data persistence...

MySQL NOT IN Query problem

I am trying to search a table where the field name does not have a list of strings in it. I use: SELECT * FROM members WHERE name NOT IN ('bob', 'jim', 'leroy'); but it still returns matches containing those words. I have searched high and low for the answer to this. Can anyone help? ...

insert into a MySQL database on a different server

Using PHP and MySQL in a script run on mysite1.com I'm trying to copy all rows and columns from a table on mysite2.com into an identical table (that has already been created) on mysite1.com. First I connect to both the databases (I've already set up remote MySQL connections on mysite2.com). $con1 = mysql_connect("mysite1.com", "username...

I have 3 tables how to join them to generate a table?

Table a (Author Table) author_id author_name Table b (Post Table) post_id author_id Table c (Earning Table) post_id (post id is not unique) post_earning I wanted to generate a report consist of earnings per author. author_id author_name total_earning (sum of earnings of all the posts by author) The SQL Query used: SELECT...

PHP PDO using MySQL driver doesn't work - MAy be my code?

Okay I'm trying to convert MySQL request to PDO MySQL. I already do that before but I cn't get this one working : $query = 'SELECT key FROM turl WHERE key = "'.$k.'"'; $req = $db->query($query); if($req->rowCount() > 0) { $key = $row['key']; } And here are PDO php extentions : extension=php_pdo.dll ;extension...

PHP do not repeat last fetch.

Hi, I have this code: $result = mysql_query("SELECT * FROM quote ORDER BY RAND() LIMIT 1") or die(mysql_error()); $row = mysql_fetch_array( $result ); echo $row['frase']; It echo´s a random "frase" every time you query. What I want to do is avoid having the same result in consecutive queries. To make myself clear, if in my data...

ORDER BY the quantity of records with a timestamp of < 24 hours (MySQL)

I have a table full of entries with DATETIME time stamps, and an ID field. I want to make a MySQL query (in my PHP script) that selects from another table with the matching ID. I want to make some kind of join that will sort by the number of entries in the first table with a timestamp more recent than 24 hours. So basically, if there are...