mysql

PHP/PDO: use simple prepared statement with query return/affected rows?

I am new to PDO objects and cannot find a single piece of documentation that will help me. Say I got a simple code to delete a row: $count = $dbh->exec("DELETE FROM fruit WHERE colour = 'red'"); That will return affected rows, but how would I use prepared statements with that? Can use use $dbh->prepare AND $dbh->exec or query !? ...

What is the best way to delete an array of values from an array?

I want to be able to easily and quickly delete an array of needles from a haystack array. In actual fact I have a comma separated list of numbers (though this is a string i know) that need to be deleted from a second comma separated list of number that I pull from a field in my sql database. So I need - $orig_needle_list = "3456,5678...

PHP: How to make a list of film and genre (example) - MySQL Join Many-to-Many (want?) and the PHP?

Hello everybody! How to make a list of film and genre - MySQL Join Many-to-Many (should I?) And the PHP? Or do the other way? Examples: In MySQL: Table 1: movie_id | movie_title 1______ | title_movie_1 2______ | title_movie_2 3______ | title_movie_3 Table 2: genre_id | genre_title 1______ | title_genre_1 2______ | title_genre_2 ...

When you auto-create subdomains on user signup does it create a new website or gives appearance of a website?

Hi, I am wondering if subdomains that are created upon signup become websites or do they just give the illusion that they are websites sort of like example.com/username? I am trying to create something like user.domain.com and can't really find something helpful to do it? Is there a way I do it so that i can allow customizations for ...

Optimizing a MySQL query to avoid "Using temporary" and "Using filesort"

I know there's a thousand similar questions out there, but none deal with as convoluted a query as mine (and my MySQL skills aren't to the point to really understand how to adapt them.) Here it is: explain select `ev`.`EventID` AS `EventID` ,`ev`.`EventName` AS `EventName` ,concat(`ev`.`EventDate`,' ',`ev`.`StartTime`) AS `EventD...

Most performant alternative to SELECT COUNT(*) using PDO and MySQL

I've read previously that SELECT COUNT(*) is not performant in MySQL and should be regularly avoided. (As a side question, is this only the case for a large number of rows?) I'm using PHP Data Objects (PDO) to access MySQL. How should I be counting rows with performance considerations in mind? ...

Rake trouble finding MySQL after uninstalling Rails 3.0.0

So I thought I had fixed the problem posted in this question and I uninstalled Rails 3.0.0 with sudo gem uninstall rails -v 3.0.0, but then I had troubles with other things. I took rogerdpack's advice to a different level and uninstalled all of my ruby gems and mysql, then reinstalled them. Now I get the following: Icarus:temporary atg...

calculate rank in highscore from 2 tables

i have a trivia game and i want to reward users for 2 events: 1) answering correctly 2) sending a question to the questions pool i want to query for score and rank of a specific player and i use this query: SELECT (correct*10+sent*30) AS score, @rank:=@rank+1 AS rank FROM ( trivia_players JOIN ( SELECT COUNT(*) AS sent,...

MySQL bulk value insert

I have a bunch of emails, each separated by a comma (,) (not csv). I want to upload them to a database table (with single field email) such that each email goes into separate record entry. what could be the most easiest way to do that? I have an idea of using grep to replace commas with my sql syntax.. but searching for any other workaro...

Configure mail server to work with PHP.

Hi. My current project is a form that receives input from a user. After I receive that data, I must send a warning/report to a few email addresses, including the user who inserted the data. Almost everything is up and running, Apache, MySQL, PHP. Now I never installed a mail server, to work with PHP so I'm kinda lost. My employer has ...

Appending and importing data

Hi, I am exporting from one table and importing to another table. First i import 100 records, then i need to import next 500 records. Instead of appending it imports that last 500 records, i want to append 100 +500 records. Is there any option in that in mysql? Thanks. ...

Storing user action data in MySQL: one table or many?

I am building a website wherein the users can perform a variety of actions and they get a variable number of "points" or "badges" for performing specific actions. Certain data must be stored no matter which type of action the user performs, such as the user ID, the action type, a timestamp, the current point total, and any badge awarded....

How can I implement the following SQL query as linq to entities query?

How can I implement the following SQL query as linq to entities query? select *, MIN(QueuedDate) from pcm_transactions where QueuedDate IS NOT NULL And ExecutionDate IS NULL group by SimId I spent hours thinking and trying varius methods - hope to find the right answer here. EDIT: Here is one of my first tries: // Get the oldest q...

Database and DB engine for social website

For a heavy user content website with user profiles, live feeds, photo /video/content sharing, etc what DB to use, and what DB engine? Ofcourse Oracle/Microsoft sql are out because they are not free (or cheap). I am using MySQL with MyISAM but that will run into performance issues on a social site. Even using InnoDB may not help performa...

Math function from php to MySQL

I need to use this function form php directly to mysql i'm currently using a simpler formula but this one works better, the porpose is to find the geodetic distance from two point on a map, so basically in my mysql i have a table with cities and avery city has its own lat,long values... his is the formula i would translate function di...

Easiest way to duplicate rows in a table, subtable, and subsubtable

I am implementing a "Save As Copy" function for a small web app that uses MySQL. Let's say I have three tables, like so... TABLE Doc ID, title, text TABLE DocAttributes ID, DocID -> Doc(ID) title, text TABLE DocSubAttributes DocAttrID -> DocAttributes(ID) title, text What we have here is a situation where a single Documen...

serializing an array and storing in DB

Hi, i have an array like $newArray = $_POST[$newId]; print_r($newArray); it prints like Array ( [1] => Yes [2] => a [3] => b [4] => c [5] => d [6] => e [7] => f [8] => [9] => [10] => [11] => [12] => [13] => [14] => ) but when i try to store in in db after serializing like serialize($newArray) it get stored like s:211:"Ar...

Basic Ruby SQL Problem Error undefined method `[]'

I think I'm being stupid. rc = Mysql.new('CENSORED_HOST','username','password','db') release = rc.query('select * from wp_ribcage_releases where release_id = 1 limit 1') puts release["release_title"] rc.close And I am getting the following error: ribcage-connect.rb:17: undefined method `[]' for #<Mysql::Result:0x1011ee900> (NoMetho...

Sorting MYSQL Tag table

Hi there, just wondering if it is possible to get the top 10 COUNT results and ordering by COUNT and alphabetically? I have the following tables, tags ------- id | title . tagged ------ tag_id | post_id And the following SQL query SELECT tag.*, COUNT(td.tag_ID) AS tagcount FROM Tagged td LEFT JOIN Tags tag ON td.tag_ID = tag.tag...

Insert and display non-english fonts in mysql

Hi! I've an excel file with non-English characters in it. when i open the excel file, i get random English characters. But when i change the font to that language, i can read valid text. But the problem is, when i load the data from that excel file to mysql through php, i get only those random English characters. I'm not getting the la...