mysql

Getting total number of records from mysql table - too slow

I have a file that goes thru a large data set and splits out the rows in a paginated manner. The dataset contains about 210k rows, which isn't even that much, it will grow to 3Mil+ in a few weeks, but its already slow. I have a first query that gets the total number of items in the DB for a particular WHERE clause combination, the most ...

using foreach loop for multiple MySQL inserts

I'm using the following snippet to break up an string array, then insert them into the database. //split tags into individual words $tag_array = explode(',', $tags); foreach($tag_array as $tag){ addslashes($tag); echo $tag." "; $addTagsQuery = "INSERT INTO `my_blog`.`tags` (`id`,...

Mysql auto increment alternatives

Hi, I have the below table. create table mytable( physical_id int auto_increment, logical_id int, data varchar(20), version_start_date datetime, version_end_date datetime, primary key(physical_id), unique key(logical_id,version_start_date, version_end_date) ); The idea behind the schema is, I want to keep track of modification to ev...

insert record if not exists in sql, duplicate column name

i wanted a solution to insert a record if it isn't there so i searched here and found a solution but i have another problem INSERT INTO closed_answers (question_id, subject_id) SELECT * FROM (SELECT 2, 2) AS tmp WHERE NOT EXISTS ( SELECT question_id FROM closed_answers WHERE question_id = 2 AND subject_id = 2 ) LIMIT 1 the output...

Save Checkbox State and Update Database

Hello, I am stucked with an idea I want to implement into one of my projects, and I need some help from the best. :) What I have In the first image I pull some info from my DB and display that info and also a button do Edit/Remove that Even and a Checkbox that should be checked in case of the Event is finished/done. In the second...

A ideal data type for long utf8 strings in mysql

Hi! Is it true that VARCHAR type in mysql only suppory 255 characters data length? if it is true what kind of datatype for UTF8 strings is useful for long texts? I'm using utf8_persian_ci datatype. Note that TEXT datatype have problem with utf8_persian_ci. ...

Is 154 MySQL Queries in 0.543 seconds a horrible thing?

I'm writing a wordpress newspaper theme. Because the theme makes use of multiple loops, it generates a lot of queries. Plus, I have to make many options dynamic because the theme will be released to the public (so I can't hardcode a lot of stuff). On the front page, with a typical setup (a top story area, five newsboxes [that's five wo...

Fix length number in mysql

Hi all, I want to have a field in a Mysql table, which should accept inputs having a fixed size - no more, no less. The input data is a number, but solutions for strings can also be considered, as I have no problem storing this data as varchar like stuff. To be exact, I want a datatype which will NOT allow me to store a number which i...

php 's mysql_query function didn't support more LIKE statament ?

I queried: select * from marrydays where YMD_X like '2010-1-%' and marrydays.CONG not like 'aa%' and marrydays.CONG not like 'bb%' ; But after I use mysql_fetch_object function,I didn't got exact result,the result just like I queried : select * from marrydays where YMD_X like '2010-1-%' ; Why? ...

How to send link id's to a sql query using jQuery?

Hello, I am trying to send link id's to a sql query in order to filter data. When I click a link like the ones below, I want to send the id of the link to the sql query as shown below: Example: Links (clicked): <p>Fitler Results</p> <a href="#" class="category" id="marketing">Marketing</a> <a href="#" class="category" id="automotive"...

Is there a way to set the default database handle with mysql_query?

Is there a way to set the default database handle with mysql_query? So I know that mysql_query can be called without a sql handle but then it will basically use whatever handle you got from the last mysql_connect. What if I wanted to set that default handle myself, how do I do it? I just want to make it clear that we have all our code...

mysqli_fetch_array returning less results than expected.

I'm using the code below to return and echo an array. If I define ' mysqli_fetch_array($results, MYSQLI_BOTH) ' then my array is truncated by one result. The 1st result in the array drops off the list. If I remove the MYSQLI_BOTH then I get the results that I expect, but my hosting company (Dreamhost) throws this error: Warning: mysqli_...

Free database with places, parent places, latitude, longitude, etc.

Where to find free database of wolrd places with following fields: id place name in English place name in original language place type - city, region, state, country, continent, etc. latitude longitude parent id - id of place which current place belongs to, for example, San Francisco belongs to California, but California belongs to US...

VB.NET, Castle ActiveRecord and MySQL: Duplicate entry in a unique field

Hi. When user inserts a duplicate entry in a unique field, Castle ActiveRecord throws the following exception: Castle.ActiveRecord.Framework.ActiveRecordException was unhandled Message="Could not perform Save for SerieNotaFiscal" Source="Castle.ActiveRecord" StackTrace: at Castle.ActiveRecord.ActiveRecordBase.InternalSave(O...

MySQL CREATE FUNCTION fails on a shared webserver

Hi, I have the following function. When I try to create it on a webserver, it fails with You do not have the SUPER privilege and binary logging is enabled (you might want to use the less safe log_bin_trust_function_creators variable) I never had the same problem with any other webhosting (and the exact same function), how can I fi...

MYSQL why would min be larger than max?

select min(lead), max(lead) from products WHERE part_line != 90 and category = 'x' When I run the above query the min returned is larger than the max. If I remove the condition pline != 90 the problem goes away. I was thinking at first that mysql was processing the min, max before it got to pline... but that doesn't explain why it w...

Hibernate optimistic locking..how it works??

Hi, I was reading the below blog about hibernate optimistic locking. I am planning to use it with hibernate. But, I have one concern. we have java code and c++ code, both connect to one database. While, java code can use hibernate to achieve optimistic locking, I want to make the c++ code do the same thing. Also, c++ code is using some ...

Getting Logged Out During Navigation

Hello, I am using a PHP / MySQL login system. With a new browser session, I tend to get logged out as I navigate the site. Any tips on how I can go about fixing this? Thanks in advance, John W. ...

Mysql custom sequence generator ( like oracle)

I want to have two auto_increment column's per table, but mysql allows only one auto_increment columns. So, I tried replicating the oracle sequence using my own table. Here is the schema. create table logical_id_seq ( logical_id int auto_increment, primary key(logical_id) ); create table mytable ( physical_id int auto_incr...

Handling the '.' character passed to a MySQL query via url

I've hit a bit of a snag with one of my projects. Like a lot of nerds, I decided to create my own video game review site. Reviews are stored in the database, and can be retrieved via the title of the game with a url like: http://www.example.com/reviews/{gameName}/{optional pageOfReview} Unfortunately, when testing edge cases, I came ...