mysql

Do I have to use mysql_real_escape_string if I bind parameters?

I have the following code: function dbPublish($status) { global $dbcon, $dbtable; if(isset($_GET['itemId'])) { $sqlQuery = 'UPDATE ' . $dbtable . ' SET active = ? WHERE id = ?'; $stmt = $dbcon->prepare($sqlQuery); $stmt->bind_param('ii', $status, $_GET['itemId']); $stmt->execute(); $stmt->close(); } } Do I need to mysql...

MySQL selecting distinct names from two columns

I have two columns of coaches names: coach1 JOHN JACOB MARY coach2 JOHN JACOB HENRY I would like to select all DISTINCT values between the two columns. So that my SELECT statement will read, JOHN JACOB MARY HENRY with no duplicates. Any suggestions as to the most efficient way to do this? ...

change a field in mysql table?

i want all fields that got a certain value to be changed to a different value. but my code gave me an error UPDATE maps SET city_id = $new_id WHERE city_id = $old_id you cant write it like that? I get this error code: Couldn't execute query: You have an error in your SQL syntax; check the manual that corresponds ...

How do I insert a date into mysql as a parameter?

I have the following code: function dbInsert() { global $dbcon, $dbtable; global $inputData; $sqlQuery = 'INSERT INTO ' . $dbtable . ' (id_author, date, title, description) VALUES (?, ?, ?, ?)'; $stmt = $dbcon->prepare($sqlQuery); echo $sqlQuery; $stmt->bind_param('isss', $inputData['idAuthor'], $inputData['date...

Lob returns null using MySQL and jpa

Hi folks, I'm trying to store a document as a BLOB in a java domain object that I have. I've seen several posts saying it can be done using @Lob for example http://www.java2s.com/Tutorial/Java/0355__JPA/MarkByteArrayFieldAsLob.htm So, the problem I have is, when I store the data all seems fine, I can see it in my mysql database, howe...

Can primary index be a CHAR in MySQL?

My primary indexes are unique reference numbers like 002345 and 000023. If I format them as integers I loose my zero's. They need to be 6 digits. Can I use CHAR? I don't need any auto increments. ...

groupby msql query

Hi I have a bunch of ID's. I can assign them once a day. I need to query my database, see if they're any ID's available to hand out (if I didn't hand out all of them in a 24 hour period), and if there's any available, write a new row to the database saying that the ID has been handed out. I also need to know which user held which ID at...

Python MySQL query not completing

Hello. I am having problems with a Python script which is basically just analysing a CSV file line-by-line and then inserting each line into a MySQL table using a FOR loop: f = csv.reader(open(filePath, "r")) i = 1 for line in f: if (i > skipLines): vals = nullify(line) try: cursor.execute(query, vals) ...

how to reuse deleted primary keys in mysql?

i've got an column named 'id' in a mysql table which also is a primary key that auto-increments. when i delete rows their id's will also be deleted thus creating "holes" in my id sequence, eg. 1, 2, 3, 9, 10, 30 and so on is there a way of reusing these deleted id:s? ...

Help in designing simple 2 table database?

Trying to create a simple 2 table database that lists the contents of various firewire drives, one table with FW number, the other with a file item on that drive number. eg. fw 233 file -> mike's home movie.mov I'm very new and can create single tables with queries, but haven't understood querying multiple tables with join. each d...

When exactly are PHP-MySQL variable assignments made?

[Status: learner] Using checkboxes, the user selects rows from a displayed HTML table. I then want to insert the date and the unique row id ("select_id") of the selected ('ticked') rows into a different table. Maybe I'm off-base, but it seems to me that the first block of code does not work. The second block of code does work. If I'...

How to setup database tables with multiple similar attributes

There are products, sections and attributes. Each attribute can have up to 5 or 6 options. Example: Power 10 Volt 15 Volt 20 Volt And there are about 10 products in total, each product has up to 17 attributes applied to it. Example: Product 1 power - 10 volt color - red, yellow link - online, offline How would you setup the tabl...

SQL: SUM and GROUP BY don't return any row on ASP

I have two tables as follow: t_product p_id, p_name... t_order o_id, o_product, o_quantity that is my query: SELECT t_product.*, t_order.* FROM t_product JOIN t_order ON p_id = o_product ORDER BY o_product it return: p_id | p_name | o_quantity --------------------------------- 01 | prod_01 | 30 01 | prod_...

Ordering the rows of a database table

I have a table feeds that contains a collection of code-description rows. e.g. Key | Value | Url fb | facebook | http://www.facebook.com tw | twitter | http://twitter.com ff | friendfeed | http://friendfeed.com A REST endpoint returns the contents of this table as a JSON array & it is displayed ...

How can I find one of many possible patterns among a single mySQL entry? More inside.

I had a hard time summing up my question. Basically: There's a table called "files". Files holds an entry called "grades". It is used to identify the particular grade level a file might be useful for. Because a file can be useful for > 1 grade level, I store things like this If it's only good for 3rd grade grades: 3 If it's good for 3...

mysql database join based on a mapping

Hi, guys, An abstraction of the problem is like this: I have one table having a column called 'country'. the value stored are name of the country, e.g. US, UK.. I have another table having a column called 'country_code'. the value stored are numerical representations of the country, e.g. 12, 17... how can I perform a join operation (...

how to set default-storage-engine=InnoDB in XAMPP for Linux1.7.3a

can any body plz help me...? ...

SubSonic 3 and MySQL, removing underscore from column name in CleanUp() method causes exceptions when using property in linq-query.

I've run into a problem when using SubSonic 3(.0.0.3) ActiveRecord with MySQL. Since MySQL doesn't allow you to use uppercase letters in table or column names (or rather disregards it if you do) I decided to separate words using underscores, e.g. entity_id, and then use the CleanUp() method to add title casing and remove the underscores...

How to insert latin characters into Mysql from the command line shell ?

Why can't I insert the word Español via the command line mysql program ? See session below. mysql> select CONCAT( 'Espa', 0xF1, 'ol' ); +------------------------------+ | CONCAT( 'Espa', 0xF1, 'ol' ) | +------------------------------+ | Español | +------------------------------+ 1 row in set (0.00 sec) mysql> crea...

Make a fake schedule if it doesn't exist in db

I want to pull all the rows from a database for a month and if there are any rows in the beginning or in the end that are missing then I want to create fake rows, so every day there is a schedule that one has to follow. How can I detect missing rows or how can I solve this issue? ...