mysql

preventing duplicate row insertion in php/mysql

how do you prevent the entry of duplicate records in php mysql? validating input before insertion to the table. i am currently building a user registration section for my website but want to present them with an error if a duplicate username or email address is found in the database. any ideas would be appreciated... thanks! ...

What is the standard way of dealing with 'THE' in DB field names

I have a database full of band names. Many of these start with "The" (eg "The Strokes"). Should I split out the "The" portion into its own DB field or keep it all as one name and handle "The" sorting/searching with SQL? Originally I had split out "The" from the beginning of the band names and put it in a boolean flag field. I did thi...

What's the best way to manage file/item relationship?

I'm working on a CMS basically because I want to learn how to build one from scratch, and am at the point where I have to stop and think about how I want to manage the relationship between files and let's say, for the sake of simplicity, blog entries. If each file is going to be associated with one and only one "blog entry" (or item), t...

How to show all the maintainance history when user click on the registration number

i want to show all the vehicle maintenance history when user click on the registration number. but, when user click on the registration number, the page only show the latest data. for example, TAC 2123 have 5 maintenance record, but when user select the registration number, it only show the detail of the latest record. how can make it s...

mysql_query returns an empty resultset, even when query is valid

Hi! When I execute the following code in a php script. The result set is always empty. However, when I execute the same query in mysql console or in phpmyadmin,it rightfully shows 18 rows in the resultset. What could be going wrong here. $result_set = mysql_query("SELECT * FROM categories WHERE 1 ", $this->database_handle); var_dump($...

MySQL, how to get the largest value of one column?

SELECT column FROM table WHERE column is largest? ...

A question about query result judgement in PHP and MySQL

Code as follows: $db = DatabaseService::getInstance(); //get a database handle,base on pdo $sql = "select * from authusers where ssouid = '".$ssouid."' order by regtime"; $res = $db->query($sql); if($res && $res->fetch()) // here is the Problem line { //do something } else { //raise some exception } In the problem line,I wa...

Switching from MySQL to Cassandra - Pros/Cons?

Hello all, For a bit of background - this question deals with a project running on a single small EC2 instance, and is about to migrate to a medium one. The main components are Django, MySQL and a large number of custom analysis tools written in python and java, which do the heavy lifting. The same machine is running Apache as well. Th...

mysql query for getting all items and removing one item based on a criteria

so here's what I accomplished right now: given email1 and email2, get all emails that are not equal to email1 create a list of them check if email2 exists in the list if it does return false, if it does not return true Is there a way to accomplish this via a query instead of creating a list and searching if email2 exists in it? (beca...

Webapp works on my windows dev box but not on server which is running Debian

I have a web application based on a mysql database. It works fine locally but not on the unix machine. I have tried looking at the Tomcat logs but they don't seem to have any errors. How would you go about trying to debug this. I know it' possible that there are no answers. I am just hoping that someone had a similar problem, can post...

Storing checkbox values in a MySQL database then retrieving whether they're set or not... what is the best method?

Hi everyone I have a form where users can submit sites and say if they work in different browsers. Currently, it looks like this: <input type=checkbox name="browsers[]" value="IE6"/>Internet Explorer 6<br/> <input type=checkbox name="browsers[]" value="IE7"/>Internet Explorer 7<br/> <input type=checkbox name="browsers[]" value="I...

Migrate a SQLite3 database table to MySQL with Python without dump files

I need to migrate information that I created in SQLite3 to a MySQL database on my website. The website is on a hosted server. I have remote access to the MySQL database. I initially thought it would be easy, but I am not finding any good info on it, and everything I read seems to imply that you need to dump the SQLite3 file, convert i...

How to avoid mysql 'Deadlock found when trying to get lock; try restarting transaction'

I have a innoDB table which records online users. It gets updated every page refresh by a user to keep track of which pages they are on and their last access date to the site. I then have a cron that runs every 15 minutes to DELETE old records. I got a 'Deadlock found when trying to get lock; try restarting transaction' for about 5 minu...

Storing serialized ruby object in database

I would like to store very large sets of serialized Ruby objects in db (mysql). 1) What are the cons and pros? 2) Is there any alternative way? 3) What are technical difficulties if the objects are really big? 4) Will I face memory issues while serializing and de-serializing if the objects are really big ? ...

Is this php code safe ?

Hi , I am aware I should be using prepared statement's but my next project will be using prepared statement's I just need to finish this simple little application. So my question is: Is this following snippet of code secure ? I have used htmlentities aswell as mysql_real_escape_string because I thought it was a safe option. //Imag...

Why does MySQL return two different data sets if I do a `WHERE field=1` and `WHERE field='1'`?

I noticed something weird -- MySQL is returning two different data sets when I do a query with WHERE field=1 and with WHERE field='1'. The field is defined as enum('0', '1', '2'). How is it possible that I get different data sets? For the first query I get roughly 500 records. For the second I get 19 (!!!). I can't explain it. Any i...

Checking if a url is already submitted in database in php

Hello everybody, What i need get done is checking if a url is already submitted in database using php. I have a database table where i store URLS submitted by users. But i want to check if the urls are already submitted or not in the database before inserting it to the database. For example, URLS like http://www.example.com, http://www...

datetime searching using sub-sections of the YYYY-MM-DD HH:MM string

[MySQL/PHP] My table has a date column of datetime format. All records are of the YYYY-MM-DD HH:MM:SS variety. MySQL queries like SELECT record FROM table WHERE date > '1941' AND date < '1945' work nicely. MySQL queries like SELECT record FROM table WHERE date > '1941-03-01' AND date < '1945-01-30' also work nicely. But what about if ...

OUTER APPLY is not supported by MySQL

I am using Entity Framework with MySQL. The following query results an error: var foobar = ctx.ArticleBase.OfType<ActicleSpecial>().Include("CreatedBy.Image.Location").ToList(); Error: "OUTER APPLY is not supported by MySQL" I also get the "CROSS APPLY is not supported by MySQL" on a little different query. I have the following datam...

How to implement a database GUI in java

I want to write a java program that acts as a user interface to a mysql database,the program should do the following: 1.connect to the database,show available tables in the database 2.display table data 3.modify table data (insert,edit,delete,sort) rows I've tried to use JDBC only,but couldn't figure out a way to put the table data i...