mysql

mysql is array in multiple columns

I have a string in the form $string = 'London,Paris,Birmingham' and I want to search multiple columns for occurences of these values. For example WHERE events.name, events.cities, events.counties IN (".($string).") Can someone recommend me a simple and short way of doing something like this. ...

How can I get all values of a mysql table in JSON from a php script?

This is php script that fetches a table values from mysql (one row). & echoes it as JSON <?php $username = "user"; $password = "********"; $hostname = "localhost"; $dbh = mysql_connect($hostname, $username, $password) or die("Unable to connect to MySQL"); $selected = mysql_select_db("spec",...

MySql syntax question regarding CONCAT and strings

Environment is MySql 5.1.5. This is a snippet from a larger stored procedure. Assume the variables are properly declared and set before this code is reached. When I run the following loop, something seems to be failing in the CONCAT. @columnName and @xmlQuery are both VARCHARs. When I "SELECT @xmlQuery" at the end of the procedure, ...

MySql : Break string by delimiters (^A)

I have a string that is of the: type 'field1^Afield2^Afield3^A' I need to break this string and get one of the fields. All the string functions I see in MySql that help you break by delimiter expect a string as the delimiter , example: SUBSTRING_INDEX("Hello. Break this. for me", ".", 1) would give = "Hello" How do i break mu string...

Error on specific search query.

Hi I have a website ongrounds.com there is a search bar on top when ever I search for word "best" it generates following error Warning: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '%s) AS relevance FROM hotaru_posts WHERE (post_status = %s OR ...

What are some good, fast persistant storage options for key->value data?

For a small PHP app I am writing, we need to store a large number of records that have a hash key, and a few simple field values ('host', 'path'). eg: '4420ffb32a' => array( 'host' => '127.0.0.1', 'path' => 'path/to/resource', ); What is the best persistent storage for data like this? Would MySQL be the best choice, or is it overk...

Fatal error - execute() php 5.2.13

Hello there, I have PHP 5.2.3, apache2.x, mysql 5.x on a Linux machine. EVerything was working fine untill yesterday. This morning all of a sudden one of the php files started to throw "Fatal error: Call to a member function execute() on a non-object in". I use PDO (prepare, execute, close cursor etc). Have you ever come across this ...

Performance: UUID vs auto-increment in cakephp-mysql

I was searching if UUID generated by cakePHP (32 char long) is faster in performance compared to auto-increment. Comparison in both inserts and Select operation. Which one should I use UUID generated by cakePHP or by using simple auto-increment of MySQL Here's is a case-study I found but its not specific to cakePHP http://krow.livejour...

Code sample to remove all special characters from data stored in MYSQL ?

Does anyone have a code sample where I can to remove all special characters from data stored in MYSQL? I need to remove the following special characters: : ~!@#$%*()_+{}[];':"<>? ...

ERROR: check the manual [...] MySQL server version for the right syntax to use near '

So I have no idea what the deal is here... the following code below produces the mysql error following the code. $fcontents = file("inventory.csv"); for ($i = 1; $i < sizeof($fcontents); $i++) { $line = trim($fcontents[$i]); $arr = explode(',', $line); $values = implode(',', $arr); $values = str_replace('&', 'and', $va...

How to check that a value is unique before importing a row into mySQL?

I have a database table that I need to import a bunch of records into. I don't want to overwrite existing records if they're already in the database, so I've set things up to do a select query first to check if there's a value, but apparently the rows are importing too quickly for the index to keep up, as I'm getting duplicates created f...

PHP PDO fetch results

I have the following script which runs fine but I think a) it is ugly; b) it doesn't do what I need after the script is run. Utlimately, the script should work as follows: 1) SQL selects all agents from a given table where the enrollment date is within the last month, grouped together by agent number where the count is greater than ...

MySQL 'Between' dates not selecting the correct results using PHP (and Codeigniter)

Here is my code (using CodeIgniter): $now = date('Y-m-d'); $then = strtotime($now . '-1 week'); $then = date('Y-m-d', $then); $q3 = $this->db->query("SELECT * FROM posts WHERE publish_date BETWEEN '$then' AND '$now'"); $data['posts_today'] = $q3->num_rows(); I clearly have poste...

Delayed for some heavy users of a billing details page

I've developed a billing summary page use mysql + php. there are many users : (1M) light user :each has less than 10K record :0.99M users heavy user :each has about 1M record SQL is the following : SELECT SUM(value_a) A, SUM(value_b) B, SUM(value_c) C FROM daily_data_sep_2010 WHERE user_id='<user_id>' AND type IN ( 'type_a', ...

MYSQL insert string from html's text area with semi-colon...advise?

Hey ya guys This is in regards to PHP, Mysql and Html's textarea. I want to dump a user's input from html's textarea into my mysql database. The situation is that if the user has semi-colons within the text itself, mysql will reject the insert command for obvious reasons. What I want to know is that are there any ways to force mysql to...

PHP MySQLi Stored Procedures return call

I cannot figure a way to retrieve the return variable in PHP. It works just perfect in MySQL, but this from PHP doesn't for some reason. '; if ($this->multi_query("CALL testOut(@outvar); SELECT @outvar;")) { do { if ($result = $this->store_result()) { ...

Incorrect Use of JDBC Connection Pool

I am running a Spring MVC application, backed by a MySQL database which I am accessing using JDBC. I have been using the same code for a while, and have never really taken a look as to whether or not I was using it correctly(using connection pools properly,etc). I am aware that there is the JDBCTemplate out there, and I have considered ...

Query is resulting in 1 row with null values when id is not found

The exact query: SELECT coupon_coupons.code, coupon_coupons.discountType AS 'type', coupon_coupons.discountAmount AS 'amount', coupon_coupons.discountApplied AS 'applied', coupon_coupons.description, group_concat(coupon_targetsku.sku separator ';') AS 'targetsku' FROM coupon_coupons LEFT ...

Recomendations for PHP & MySQL Statistics Reporting for a WebApp

background: I have 'inherited' a php webapp in my small company and after years of nagging have finally gotten the go to throw away the spaghetti code and start again. We want to log every action that is made in the system for example: user X viewed item Y user X updated item Y new item Y on city Z and later provide graphs on diffe...

MYSQL - Concatenate two tables

I have two tables as follows: TABLE A TABLE B StuID | actid FacID | actid 3 12 98 17 5 17 54 21 I want to list the name of everyone, both students and faculty, who participate in activity 17. Is there anyway I can get a result as below: ...