mysql

Is this query safe from sql injection?

The script is in PHP and as DB I use MySQL. Here is the script itself. $unsafe_variable = $_GET["user-input"]; $sql=sprintf("INSERT INTO table (column) VALUES('%s')",$unsafe_variable); mysql_query($sql); Some people say that if user assigns ;DROP TABLE blah; string to the variable $unsafe_variable it deletes the table. But I tried th...

MySQL Table Loop using PHP

I have an online form which collects consumer data and stores in a dedicated MySQL database. In some instances, data is passed in the URL under the "RefID" variable which is also stored in the database and attached to each registration. I use the 'mysql_num_rows ($result)' to fetch all agent details on another page but this only return...

How do we greatly optimize our MySQL database (or replace it) when using joins?

Hi there, This is the first time I'm approaching an extremely high-volume situation. This is an ad server based on MySQL. However, the query that is used incorporates a lot of JOINs and is generally just slow. (This is Rails ActiveRecord, btw) sel = Ads.find(:all, :select => '*', :joins => "JOIN campaigns ON ads.campaign_id = campai...

How to limit a user to entering 10 keywords or less using PHP & MySQL?

I'm trying to limit my users to entering at least 10 keywords and was wondering how would I be able to do this using PHP & MySQL with my current Keyword script? Here is the add keywords PHP MySQL code. if (isset($_POST['tag']) && trim($_POST['tag'])!=='') { $tags = explode(",", $_POST['tag']); for ($x = 0; $x < count($tags); $x++){ ...

Suggested method of Internationalization in MySQL?

I have a website on Kohana and I'm planning to have it multi-language. Now I know the way with i18n folder and inside each language folder there's some sort of strings.php file.. But I want a dynamic way (on DB) so I could change the values whenever I want through the website. Is there any common table schema that is usually used f...

Partitioning a table in mysql after creation

I have a table with a bunch of data already in it. I know how to create a partitioned table or alter an already existing partitioned table, but can I add partitions to a table after it has been created, has data in it, without losing the data? The other option is to dump all the data, recreate the table with the partitions and then ins...

Select 2 Rows from Table when COUNT of another table

Here is the code that I currently have: SELECT `A`.* FROM `A` LEFT JOIN `B` ON `A`.`A_id` = `B`.`value_1` WHERE `B`.`value_2` IS NULL AND `B`.`userid` IS NULL ORDER BY RAND() LIMIT 2 What it currently is supposed to do is select 2 rows from A when the 2 rows A_id being selected are not in value_1 or value_2 in B....

Connector/C++ compile error

When I compile code that includes Connector/C++ headers, I get the following errors: c:\qt\2010.03\mingw\bin../lib/gcc/mingw32/4.4.0/../../../../include/stdint.h:27: error: 'int8_t' has a previous declaration as 'typedef signed char int8_t' c:\qt\2010.03\mingw\bin../lib/gcc/mingw32/4.4.0/../../../../include/stdint.h:31: error: '...

Create ordering in a MySQL table without using a number (because then it's hard to put something in between)

I have a long list of items (say, a few million items) in a mysql table, let's call it mytable and it has the field mytable.itemid. The items are given an order, and can be re=ordered by the user by drag and drop. If I add a field called mytable.order and just put numbers in them, it creates problems: what if I want to move an item betw...

How to know my username and password in mysql console

i've installed mySQL .. i entered my password , but i can't remember they've asked me for a username .. is it admin or root ? i wanna add the username and password to RubyonRails database.yml i remember i found a command that tells me the username and password in the mysql console .. but can't find it again ! that's the part i wanna ...

MySQL, Altering Table from Latin-1 to UTF-8

I would like to rid new entries into my database of Latin-1 characters and just allow UTF-8. I plan to Alter table and make the following changes: Charset: latin1 -> utf-8 Collation: latin1_swdish_ci -> utf8_general_ci The table in question has 1 million rows. Is this a good idea? What are the risks of doing this? What happens to...

MySQL update with two subqueries

I'm trying to update one column of MySQL table with subquery that returns a date, and another subquery for the WHERE clause. Here is it: UPDATE wtk_recur_subs_temp SET wtk_recur_date = (SELECT final_bb.date FROM final_bb, wtk_recur_subs WHERE final_bb.msisdn = wtk_recur_subs.wtk...

MySQL Limiting a query to one consistent value

My current query returns a table like: +------------+ value1 | .... value1 | .... value2 | .... value3 | .... +------------+ I want: +------------+ value1 | .... value1 | .... +------------+ I want to only receive all rows with the first value. Normally I would do a WHERE clause if I knew that value, and I cannot use a LIMIT becaus...

Mysql Trigger to select and delete

Hi, Is this possible, can a trigger in mysql can do a select first and then based on its result do a delete?, both on the same table. Am struggling to get it right. There are duplicate entries in a table, i need to have a trigger which selects and then deletes. Any ideas or thoughts will be really helpful. ...

Experience with direct data access components

I would like to know as to what has been the experience of using direct data access components like devart's MyDAC which allows a direct connection to the mysql server rather than through the client library or ODBC. ...

In python, is there a simple way to connect to a mysql database that doesn't require root access?

I'm writing a script to parse some text files, and insert the data that they contain into a mysql database. I don't have root access on the server that this script will run on. I've been looking at mysql-python, but it requires a bunch of dependencies that I don't have available. Is there a simpler way to do this? ...

Want to save data field from form into two columns of two models.

I have a Profile model with a hasOne relationship to a Detail model. I have a registration form that saves data into both model's tables, but I want the username field from the profile model to be copied over to the usernamefield in the details model so that each has the same username. function new_account() { if(!empty($this->data)...

MYSQL JOIN WHERE ISSUES - need some kind of if condition

Hi Well this will be hard to explain but ill do my best The thing is i have 4 tables all with a specific column to relate to eachother. 1 table with users(agent_users) , 1 with working hours(agent_pers), 1 with sold items(agent_stat),1 with project(agent_pro) the user and the project table is irrelevant in the issue at hand but to give ...

Submitting a URL into a Form without "http://", with "www.", or with neither

(EDITED) Hello, In the form below, the field for <div class="urlfield"><input name="url" type="url" id="url" maxlength="500"></div> fine when a URL is submitted that has a "http://" at the beginning of it. However, it doesn't work if a URL is submitted with only a "www." in front of it, or with neither a "http://" nor a "www." How ca...

How to calculate the number of immediate children for each node in an adjacency list

I have hierarchical data that I represent using the adjacency list model. TABLE ID parentID title I am wondering, what is the simplest way to SELECT the number of immediate children for each node? If possible, I'd like to do this in a single select, yielding a resultset like so... RESULTS... ID title childCount 1...