mysql

MYSQL: How to find out the qty of unequipped items?

Here is my DB Structure: CREATE TABLE IF NOT EXISTS `UserItems` ( `id` bigint(20) unsigned NOT NULL auto_increment, `user_id` int(10) unsigned NOT NULL, `item_id` int(10) unsigned NOT NULL, `qty` int(11) NOT NULL default '0' ) ; CREATE TABLE IF NOT EXISTS `UserEquippedItems` ( `user_id` int(10) unsigned NOT NULL, `user_it...

MySqlConnection.Open() waits indefinitely

I am using MySqlConnector/Net 5.2.7 in one of my applications written in C# for .NET 2.0. This application is deployed across different sites in the corporate network. This week, this application refused to connect to the database from a remote site suddenly. Actually, the MySqlConnection.Open() method does not return and waits indefini...

Remove duplicate entries from database with conditions

I've had a good look around but havnt been able to find a solution so hoping someone can help with this one. I have a MySQL table of results from an internal logging application which records a result from a check routine, there are a number of check routines which are identified with the tracker column: id (int)(PK), tracker (int), ti...

MySQL: What are the drawbacks of indexing an additional field?

I have the talbe like that: CREATE TABLE UserTrans ( `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` int(10) unsigned NOT NULL, `transaction_id` varchar(255) NOT NULL default '0', `source` varchar(100) NOT NULL, PRIMARY KEY (`id`), KEY `user_id` (`user_id`) ) with innodb engine. The transaction_id is var becau...

Uploading sdcard pictures to mysql from android phone

Hi, I'm try to let user to choose their profile picture from their SD card and uploading it to Mysql server as BLOB using php. Need some tutorial for the uploading part no luck on google Thanks ...

What is the simplest way to return a ROW as well as loop through the ROWS with PDO?

If I am doing an old query to return a row I would do something like this: $sql = 'SELECT id FROM table WHERE email="' . mysql_real_escape_string($email) . '" LIMIT 1'; $res = mysql_query($sql); $row = mysql_fetch_array($res); echo $row['id']; How do I do that with a Prepared Statement? I can get this far... $stmt = $dbh->prepare("...

PHP MYSQL error - "You have an error in your SQL syntax; check ... for the right syntax to use near ...

The exact error message is: 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 'where rfflag='0'' at line 1 Hi, I'm trying to get some php scripts working and it dies with the above error message. There are two locations where rfflag is used in the ...

Why does C# keep giving me -1 from a query when the console gives me the correct number from a count(*)?

MySqlCommand checkUsername = conn.CreateCommand(); checkUsername.CommandText = "SELECT COUNT(*) FROM users WHERE username='admin'"; MessageBox.Show("The count is " + checkUsername.ExecuteNonQuery()); There is more code where this "count" is actually being used, but it was not working correctly so I made this little message box pop up t...

upload videos from youtube

I'm going to store the video embed code from youtube since users can upload videos. What should be the data type of the field where I would store the video embed code. Video embed code like this: <object width="640" height="385"> <param name="movie" value="http://www.youtube.com/v/M8uPvX2te0I?fs=1&amp;amp;hl=en_US"&gt;&lt;/param&g...

php my sql search without "like" cause?

hi to all.. i am using php and mysql... i have application in which user enter any text and i want to fiind related data from database without using "LIKE" cause in my mysql query. is there any possible way to search these string in database. or any approach in mysql to do this.... Thanks in advance. ...

Please help me to sanitize php data goint to mysql

Hi friends I have a form with mysqli comnnection <label for="fullname">Fullname</label> <input type="text" name="fullname" /> <label for="photo">Upload photo</label> <input name="photo" type="file"/> and on the php ends I have $fullname = $_POST['fullname']; $uploaddir = './uploads/'; //upload file in folder $uploadfile...

MySQL Invalid use of group function on shared host

I need to know the team with highest number of matches in a certain city. SELECT COUNT(am_matches.team_id) AS matches_count, am_matches.team_id AS team_id FROM am_matches LEFT JOIN am_team ON (am_matches.team_id = am_teams.id) WHERE am_matches.status = '3' AND am_teams.city_id = '$city_id' GROUP BY am_matches.team_id ORDER BY COUNT(am_m...

Some data is modified without apparent reason when importing .cvs to a MySQL db, any idea?

So I'm impoting a 80000+ lines .cvs files to a MySQL database, using Import CVS via LOAD DATA in phpMyAdmin, and it seems to work fine, there are no error messages. Problem is, ater the import, all lines in the table, starting with line 24002 have the same number in one of my database fields, and this number doesn't even exist in the .c...

mysql query help : show field Appearance count and Appearance Percentage

What I need is query that show : How many times value in field is Appearance and how many Percentage is from the total. What I have until now is : SELECT field, COUNT(field) FROM table GROUP BY field; That show me result of how many time every value is Appearance. How can I get also the Appearance Percentage in one query ? I try...

Performance tuning MYSQL Database

How can i log which all query is executed,how many secs a MYSQL query take to execute, to increase performance of database? I am using PHP ...

PDO->bindParam, PDO->bindValue and PDO->closeCursor

So far I have been using PDO->bindParam however while reading the manual I found PDO->bindValue from what I can tell PDO->bindValue passes by value where as PDO->bindParam passes by reference, is this the only difference? $modThread = db()->prepare("UPDATE `threads` SET `modtime` = UNIX_TIMESTAMP( ) WHERE `threadid` =:id LIMIT 1"); whi...

Count hits for post using php and mysql

Hi Friends, How i can develop a hit counter for my posts. I have a mysql table with following cols id title body hits how i can increment hits by 1 whenever a user read post entry. Please help. ...

mysql - getting the last 10 unique months

I want to get the last 10 unique months where there have been events, stored as event_date as a mysql date. I am using coldfusion so could group the result but can't get that working as I would like. This is what I have so far. SELECT MONTH(event_date) AS month, YEAR(event_date) AS year, event_date from events so ideally if there wer...

mysql - what does this error mean?

Hello. I do not require this error to be solved. I just need to understand what it means so I can approach it myself. Cannot add or update a child row: a foreign key constraint fails (`db`.`transaction`, CONSTRAINT `transaction_ibfk_2` FOREIGN KEY (`product_id`) REFERENCES `product` (`id`)) INSERT INTO `transaction` ( `client_id...

How to create indexes on multiple columns

We have the following entity relationships where a User belongs to a particular Organization. My queries either look like "select * from User where org=:org" or "select * from User where org=:org and type=:type" I have separate indexes on the User class. The first query will be fine, because of the Index on the foreign key element. Does...