mysql

SELECT question

Let's say I have a table containing a list of cities: city | latitude | longitude --------------------------- XX | 34.800 | 48.550 Assuming I have an approximate location (latitude/longitude) of a user, how do I find the city that is nearest? That is, how do I find the city whose latitude and longitude is closest to the user's lat...

trying to validate user input in php

I'm trying to validate user input in php. This code will check if the values are null or not. If it is null, this will require the user to input the values that are null. When all the text boxes in the html form that came before this. This code will show the submit button, and that submit button will save the inputted data into the mysql...

MySQL query to select 2 columns using the same select pattern

I have to perform a select on a table in the following manner: I am searching for some strings in 2 columns, the search string can be in either one or the second, or in both. To select from a single column I would use something like: SELECT * FROM table WHERE TITLE LIKE '%pat1%' OR TITLE LIKE '%pat2%' OR TITLE LIKE '%pat3%' etc. Th...

Creating A Procedure

I'm at a loss, I can't see the reason something as simple as this won't work: CREATE PROCEDURE test() BEGIN DECLARE var INT; SET var = 0; END I'm literally just testing this because I can't seem to create anything at all. The error message I get is: [ERROR in query 1] You have an error in your SQL syntax; check the manual...

Testing for security vulnerabilities in web applications: Best practices?

Hi y'all I'm developing a web application. Like, a proper one, I've used things like Joomla in the past to make awesome stuff but have now finally got my hands dirty with PHP, MySQL and CodeIgniter. When you're making serious web apps that'll handle large amounts of data, what precautions should I take against my data inputs to fully s...

Record User Actions Using PHP and Javascript

I am developing an online learning system (PHP, MySQL and Javascript). I would like to track what pages and how long each users spent on each page. Ideally, I would like to record this in a MySQL database. My question is 2 fold: 1. What kind of fields would I include in my db table to record multiple pages accessed? 2. Is this problem be...

Password protect an entire directory with PHP authentication

I want to password protect an entire directory and authenticate users though a MySQL database using PHP. I know you can password protect directories using .htaccess but I can't seem to find out how to use PHP and MySQL along with that. ...

MySQL Update during CSV import question

Okay, so I have this CSV file of products and all the products have an ID number. I am going to import this CSV into an existing (empty) database table where the columns in the database are named the exact same name as the first-row columns in the CSV file. There is one extra column in the DB called URI. I need to build this during (o...

MySQL delete from 3 tables

Hi There, I have the following tables, with these keys in my database: bookings session_id sessions session_id course_id courses course_id I want to create a query to delete all date relating to a single course (course_id). For example if I wanted delete course_id=10, I would want any sessions with course_id=10 to be deleted, in add...

MySQL Syntax Error; "check the manual that corresponds to your MySQL server version.."

I've been looking all over the internet for a solution to the following error; "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 'primary, username, password, password2) VALUES (null, 'hello', 'hello', 'hello')' at line 1" I have no idea what is going...

null values in mySQL

I've got a new website moved to my server. This website uses PHP and MySQL, and is built very poorly. The problem is - it needs non-null values inserted into new records where ever I don't specify a value, though the default value is null. I've been told it has been done on the previous server, but they have no idea how. I'd be glad t...

MySQL: value based on conditions

I have a MySQL query where I would like it to have a pseudo column returned which is based off of: If columnW does not equal 0, it should be 1 BUT If columnX does not equal 0, it should be 2 BUT If columnY does not equal 0, it should be 3 BUT If columnZ does not equal 0, it should be 4 Hopefully that makes sense. My SQL isn't good en...

Implementing Recursive Comments in PHP/MySQL

I'm trying to write a commenting system, where people can comment on other comments, and these are displayed as recursive threads on the page. (Reddit's Commenting system is an example of what I'm trying to achieve), however I am confused on how to implement such a system that would not be very slow and computationally expensive. I imag...

Merge and Alias of two MySQL tables

i've got two tables: members and members_data. They both share the same unique auto increment id key and unique username fields. I would like to merge the two tables into one members table, BUT I have ~50 scripts using members & members_data references. I also need some sort of alias setup to make my members_data queries point toward t...

Is this a PHP or MySQL bug?

$allUsersResult = mysql_query("SELECT * FROM users"); // the purpose of this line was to grab the first row for use // separately in a different area than the while loop $user = mysql_fetch_assoc($allUsersResult); while($users = mysql_fetch_assoc($allUsersResult)){ // the first row is not available here } So is this a bug or is ...

SubSonic crash with MySQL provider

I'm using Subsonic 3.0.0.3 with ActiveRecord, running against a MySQL database. I'm finding that this works fine: // Approach 1 var db = new testDB(); db.Insert.Into<company>(c => c.name) .Values("Acme") .Execute(); But this crashes: // Approach 2 company c = new company(); c.name = "Acme"; c.Save(); The crash is not immediate...

MySQL Result - "Group By" removing incorrect duplicates

Will do my best to describe the problem Im having :) Each thread/topic in my forum represents one disc. Registered members of the forum use a series of checkboxes (one displayed next to each disc) to tick each disc that they have in their collection. When the form is $_POST'ed it stores the information in a table like so: | user_id - ...

Help with writing a MySQL query involving date differences.

I need to delete rows where a datetime field is over 2 weeks old. This is what I have came up with $duration = Date::WEEK * 2; // int(1209600) $query = 'DELETE FROM properties WHERE TIMEDIFF(' . date(DATE_ISO8601) . ', reserved_datetime) > ' . $duration; I don't often write complicated queries (preferring to do stuff in P...

MySQL: How to handle regular high volume of inserts/updates?

Hi, I have table where I track various statistics about site usage. Once a week, I plan to use these statistics and information from other tables to calculate various key indicators for use in multiple reports. This is in order not to have compute the complex data each time a report is accessed. These indicators will be stored in a sepa...

Am I using Java PooledConnections correctly?

Hello, I want to use pooled connections with Java (because it is costly to create one connection per thread) so I'm using the MysqlConnectionPoolDataSource() object. I'm persisting my data source across threads. So, I'm only using one datasource throughout the application like this: startRegistry(); // creates an RMI registry for...