mysql

Persistent database connection for Ajax Calls

I have ajax call which pulls from the processing script 'getajax.php'. Call is made to 'getajax.php' script which has the db connection details, select, functions, etc. My question is: Everytime a call is received by 'getajax.php' it will go through mysql_connect, mysql_select, then queries. Is this the correct aproach to handle thou...

How should I manage connecting to a database using java servlets, JSP & Tomcat

Hi there, I'm pretty new to Servlets and JSP and to using databases. At the moment I have a class in the 'model' part of my web app which has lots of methods that I've written to perform database queries and updates. At the moment, in each of those methods I am creating a database connection, doing the SQL stuff, then closing the conne...

SubSonic column named 'Key'

Hi! I have a column named 'Key' in a MySQL database. Seems that the repo.Find<Class>(x=>x.Key.StartsWith("BLAH")); generates the SQL code WHERE Key LIKE 'BLAH%' instead of the correct one like WHERE `Key` LIKE 'BLAH%' How can I force the later behaviour (is it a bug in SubSonic?) Cheers, Tomasz ...

mysql left join question

I've got two tables, one holds reservations for a room, and the other is a "mid" table to hold the dates that the room is reserved on (since a reservation could have multiple non-sequential dates). It looks something like: Res_table: id, room_id, owner_id Res_table_mid: id, res_id, date The res_id column in the res_table_mid referen...

I need help writing a MySQL query correctly

I would like to pull up a catalog of games, but I also want to know if a game is in a SPECIFIC member's (current member in session) list of games already. This information is stored in a table called gameslist, which is a joiner table that joines members and games gameslists(id,memberid,gameid,rank) SELECT DISTINCT *, gameslists.memberi...

Wordpress WP ecommerce plugin PHP

Becuase nobody could answer my previous question; http://stackoverflow.com/questions/3483171/wp-ecommerce-plugin-show-categories-and-products-in-tree-view I'm going to do a repost. I've been working on something like this where I'm displaying the categories but now I need to loop round each category within the loop to display products...

SQL Syntax Error whilst creating a table

Hi, I have recently started learning SQL, I can't determine what is causing my syntax error (see below): CREATE TABLE Users( user_id smallint not null auto_increment, username varchar(50) unique, password varchar(41), dob date, session_id varchar not null, cookie varchar not null, PRIMARY KEY(user_id), C...

mysql 4.x -> 5.x upgrade related question

Hello All im upgrading mysql 4.x -> 5.x. and in my php source there is mysql synxtax following. $pt_sql = "select * from orderlist where condition!='delete' and left(date,10)='$nday' group by id "; how can i change above mysql syntax to mysql 5.x syntax? if anyone help me much apprecate! thanks...

Strange syntax error in MySQL

Why does this give me an MySQL syntax error: <cfset arguments.where = "platformUrlId='#params.platformUrlId#'"> SELECT * FROM whatever WHERE #arguments.where# Order By #arguments.order# But this works perfectly well? <cfset arguments.where = "0=0"> SELECT * FROM whatever WHERE #arguments.where# Order By #arguments.order# It's not...

Cache results of a mysql query manually to a txt file

Hi all, Is there a way to cache results of a mysql query manually to a txt file? Ex: $a=1; $b=9; $c=0; $cache_filename = 'cached_results/'.md5("$a,$b,$c").'.txt'; if(!file_exists($cache_filename)){ $result = mysql_query("SELECT * FROM abc,def WHERE a=$a AND b=$b AND c=$c"); while($row = mysql_fetch_array($result)){ ech...

MySql 2 rows subquery

I got mysql query like this: SELECT name, (SELECT timePing FROM TerminalPings WHERE terminalsId = Terminals.id ORDER BY timePing DESC LIMIT 1) as timePing FROM Terminals` It works good, but i need to select top 2 timePing's from TerminalPings, and name them like timePing1 and timePing2. What is the best way to do ...

How to determine breadcrumbs for products with multiple categories?

I have designed an e-commerce system whereby I have products which can belong to multiple categories. This works well for searching and browsing and allows users to find products more easily. However, I have also implemented breadcrumbs on the product page, and I don't know how to determine which category the product should be shown as ...

Relational database structure for Texas Hold'em poker data

Hello, I am planning a relational database to store poker game data (like what would be included in the hand histories). I would like help figuring out how to design the associations. It seems like there should be 4 models: Game, Hand, Player, and Action (single action of a given player, like raise, fold, call). Let me lay out what I h...

How to UPDATE the same mysql row from multiple scripts at the same time?

I am forking some php scripts and I need to update a mysql row to track some progress. I read that I should use InnoDB for this but I coulnd't find any complete example I can understand. A friend told me he uses this php code: mysql_query("SET AUTOCOMMIT=0;"); mysql_query("START TRANSACTION;"); mysql_query("SET TRANSACTION ISOLATION LEV...

PHP Update Entry Every Hour

Hi, I'm working on a site that generates a dynamic image based on data from another site. The problem is that loading the data from the other site ever time the image is accessed is slow. The Image displays the current stats of a "team" on a tournament website. I want to make a cron job and database that work together to update the info...

Import/Export updated rows in MySQL database via PHP

Hi all, I am currently having two databases that contains same data on two servers. One is a VPS and other one is a reseller account. So, what I want to do is, Import updated rows in few tables from reseller's database and export them to vps's database via PHP. I can add a 'lastupdate' field to database1 and update it's value when any...

Converting INSERT commands to UPDATE

Hey there, I have two INSERT commands, that are useless to me like that because the two sets of rows - the ones that are already in the table, and the ones I have as INSERT commands - are not disjunct. Both commands insert lots of rows, and lots of values. Therefore I get the duplicate entry error if I want to execute those lines. Is ...

Implementing site access rate limiting in PHP

Hi, I am developing a fairly simple site on a LAMP framework + Codeigniter and want to implement a user access rate limit feature to prevent the site being flooded with requests from a single user. The basic function of the site is to take a code input and return some info. I'm only at the planning stages and my first idea is to have a...

Optimizing MySQL queries / database structure

I have in my MySQL database these two tables: CREATE TABLE IF NOT EXISTS `articles` ( `id` bigint(20) NOT NULL, `url` varchar(255) collate utf8_bin NOT NULL, `img` varchar(255) collate utf8_bin NOT NULL, `name` varchar(255) character set utf8 collate utf8_unicode_ci NOT NULL, `url_key` varchar(255) character set utf8 collate u...

PHP - How to check URLS for 404/Timeout?

Hi, Here is my structure: MYSQL: Table: toys ---> Column: id, URL. How do I get my PHP script to check all of those URLs to see if they are alive or have page 404's? Try not to echo or diplay the results on page. I will need to to record in MYSQL with a extra column "checks". Results will be in this format: http://asdasd.adas --- up ...