mysql

How to make a complex named scope play nice with associations and other named scopes [rails]

I have the following named scope on class RentableItem < ActiveRecord::Base named_scope :available_at, lambda{ |starts_at, ends_at| { :select => "t.*", :from => "(SELECT ri.*, COALESCE(c1.start_date, '#{starts_at}') AS EarliestAvailable, COALESCE(c2.end_date, '#{ends_at}') AS LatestAvailabl...

Use MySQL to determine if today is a user's birthday

I have all users' birthdays stored as a unix timestamp and am wanting to send out e-mails each day to users that have a birthday that day. I need to make a MySQL query that will get all of the rows that contain a birthday on today's date. It seems like this should be fairly simple, but maybe I am just overcomplicating it. ...

Normal image storing or mySQL blob?

Hi, i'm programming a php/mysql web app which is kinda like a blogging platform where people can upload pictures and post them. What is the best way to store these images , Storing them in a folder or in mySQL table through BLOB ? i ask this because i want the most simple way which let me easily move to another host/server without spe...

What are good reasons to choose TIMESTAMP over DATETIME columns in MySQL?

Disclaimer: This might be a duplicate of datetime vs. timestamp?, but I feel I am not convinced of the answers: My question is: What are the good reasons to choose TIMESTAMP over DATETIME columns in MySQL? I'm pretty sure I don't know everything there is about the differences between those two, so I will try to list the main advantages...

PHP: I Can only use this function once (using it in a While loop)

Hi, i got an answer on an older question wich is almost working i have a function: function vraagOp($table,$where) { static $rVraagOp; if(!$rVraagOp){ $qVraagOp = "SELECT * FROM $table WHERE $where"; $rVraagOp = mysql_query( $qVraagOp ); } return mysql_fetch_assoc( $rVraagOp ); } that i want to use li...

Problem in mysqli real_escape_string

The following code results to an error Call to a member function real_escape_string() on a non-object But if place the ($this->conn = new mysqli) inside the escapedString($string) method, it will run without error. What can be the reason of the error? Thank you. private $onMagicQuote; private $conn; function __contruct(){ ...

Zend_db junction table join query

Hi guys, Not sure why I can't figure this one out. Basically, I have two tables with a many-to-many relationship so I have a junction table inbetween them. For an example, consider the following database schema: Restaurant (id, restaurant_name, suburb) RestaurantCuisine (restaurant_id, cuisine_id) Cuisine (id, cuisine_name) So, ma...

PHP MySQL Connection Failed

Hi, I am trying to connect my PHP code to MySQL safely with the following code: <html> <?php $con = mysql_connect("localhost:3306","root","password"); if(!$con) { die('Could not connect: ' . mysql_error()); } else { echo "Connection established!"; } mysql_close($con); ?> </html> But I keep getting the following error mes...

sql query date time

i have a datestamp field in my table structure. using sql, i want to find any user who registered in yesterdays date using these time range. eg: 2010-02-06 14:00:00 2010-02-07 10:00:00 i will be running this query once a day to grab users. so tomorrow will be: 2010-02-07 14:00:00 2010-02-08 10:00:00 the day after tomorrow will be: ...

Creating a default query for a column in a table (SQL)???

Hey I have a column in one of my tables which is suppose to be the total sum for from the rows of a number of tables. Is there a way i can have a default query which runs on the total sum column so that every time a row is added to the other table an update is made in the total sum column. Thanks ...

PHP/mysql application creation process

I am trying to figure out how one would start the setup of a small CMS. I have the groundwork built, but the step of creating the database tables in mysql, should this all be done at once in a install.php file? Is there a preferred method for creating many tables at once, even if I don't need to insert data into them at this time? ...

Backup multiple Databases[MySQL] at 1 time?

Hi I have multiple databases need to back up daily. Currently, I am using cronjob to set a batch file to back it up. Here are my situation, I have about 10 databases need to backup, 3 of them are growing pretty fast, let me show you the current DB size: DB1 = 35 mb DB2 = 10 mb DB3 = 9 mb the rest: DBx = 5 mb My batch file code is: ...

String value with an appostrophe in a PHP variable email address, mysql select

I have an email address mike.o'[email protected] stored in a posted variable and I want a select statement in mysql to validate the existance of the email address and retrieve a password from my table. My query gets stuck at the apostophe when it trys to execute. eg "SELECT pwd FROM tbl_users WHERE userName = '$email'"; Any assistance wi...

mysql data import from excel

how to import the data from excel to mysql database. if i convert my worksheet to csv and import it says "Invalid field count in CSV input on line 1." ...

Design question: Filterable attributes, SQL

I have two tables in my database, Operation and Equipment. An operation requires zero or more attributes. However, there's some logic in how the attributes are attributed: Operation Foo requires equipment A and B Operation Bar requires no equipment Operation Baz requires equipment B and either C or D Operation Quux requires equipment (...

Using PHP with a MySQL db, how can I select everything from a row if i dont know what the columns are?

I have a table but I dont know what the columns are except for 1 column. There is only 1 permanent data value for each row, the rest of the columns are added and removed elsewhere. This isnt a problem for the query, i just do: SELECT * FROM table but for the php function bind_result() i need to give it variables for each column, which...

Why isn't this easy join working? (mysql)

I have two tables, classified and fordon. classified table: classified_id (PK) etc... fordon table: id (PK) classified_id (FK) I try to use this code: SELECT * FROM classified, fordon WHERE classified.ad_id IN ('$solr_id_arr_imploded') AND classified.classified_id=fordon.classified_id BTW, the array is a set of ad_id:s returned fr...

Best way to avoid duplicate entry into mysql database

I have a table with 3 columns - id (pk), pageId (fk), name. I have a php script which dumps about 5000 records into the table, with about half being duplicates, with same pageId and name. Combination of pageId and name should be unique. What is the best way to prevent duplicates being saved to the table as I loop through the script in ph...

MySql DB Design question

Assuming I have an application where I expect no more than 50,000 users. I have 7 tables in my DB schema. Is it a good idea to replicate all the tables for every user? So, in my case, number of tables will roughly be 50,000 * 7 = 350,000. Is it in anyway better than 7 monolithic tables? ...

SQL Server 2000 equivalent for ON DUPLICATE KEY?

Is there a SQL Server 2000 functional equivalent to MySQL's ON DUPLICAT KEY clause? This Answer led me to look at the Instead of Trigger construct, but the sample looks clunky, and the functionality would be hidden in the trigger, and not be directly in the stored proc. FWIW: I am trying to create a stats table for per-minute summaries,...