In MySQL, I'm trying to find an efficient way to perform an UPDATE if a row already exists in a table, or an INSERT if the row doesn't exist. I've found two possible ways so far: The obvious one: open a transaction, SELECT to find if the row exists, INSERT if it doesn't exist or UPDATE if it exists, commit transaction first INSERT IGN...
Hi Chaps, Bit of a newbie, so please excuse the lack of terminology. . . . I have a PHP script to backup a MySQL database "dbjobs". I've tried nearly everything I can but can't get it to work. It works if I run the $command directly from the Command Prompt on the server, but everytime I try to run the PHP version, I get an HTTP 500 err...
Okay I have a piece of code that for some reason gives me the following error. Warning: mysqli_fetch_array() expects parameter 1 to be mysqli_result, boolean given in Here is the code. $mysqli = new mysqli("localhost", "root", "", "sitename"); $dbc = mysqli_query($mysqli,"SELECT * FROM sitename WHERE id='$user_id'"); while($row = mys...
$con = mysql_connect("localhost:".$LOCAL_DB_PORT, $LOCAL_DB_USER, $LOCAL_DB_PASS); mysql_select_db("hr", $con); mysql_query("set names utf8", $con); while(true) { do_stuff($con); sleep(50); } If connection timesout in 50 seconds,will $con still work? ...
Hi, I have the following setup: A MySQL database with a table 'transactions', containing (among other things) transaction_id, price, time and uid. I need to create a chart (I'm using flot) that will show a user his/her's sales volume (not price). I need an indicator to move the y axis of the chart. I was thinking of aggregating the tot...
I have three tables: Users, Companies and Websites. Users and companies have websites, and thus each user record has a foreign key into the Websites table. Also, each company record has a foreign key into the Websites table. Now I want to include foreign keys in the Websites table back into their respective "parent" records. How do I d...
Hello, I already have written a login and register script. The user can login to their account edit their details etc. The details are stored in a users table which has many different data properties related to the user. I was wondering, how do I make it so they can view their profile and it shows all the data they input. For example, i...
Here's the case: After computer being hacked, we are in a rush to backup all data out to other computer. As a result, the mysql databases are not backup out as sql statement. What we have done is backup out all the physical files/folders in the C drive to new computer. Eg: C:\Program Files\MySQL\MySQL Server 4.1\data In this case,...
I'm working on what is for me a complicated query, and I've managed to get the information I need, but seem to be forced to create a table to accomplish it. I'm using MySQL, so I can't use WITH, I can't use a view because my SELECT contains a subquery in the FROM clause, and I can't use a temporary table because I need to self-join. Am I...
I have a main MySQL db set up, and a class to handle the queries to it. It runs real nice. I am building a custom advertising system on my site and I'm wondering if there is any benefit to creating a separate database all together to handle that system? Is there any pitfalls to doing it either way? Option #1 - one DB for main website f...
I have some vendor data that has the SOH (ASCII character 1) as the field delimiter and STX (ASCII character 2) as the record delimiter. Is it possible to load this data with LOAD DATA INFILE without pre-processing the file and replacing those characters with something more common? ...
Can not find a command that displays the current configuration of mysql from within the database. I know I could look at /etc/mysql/my.cnf but that is not what I need. ...
Hi i have 2 fields in database and i would like to find the difference of them in percentage. So mathematical the formula should be ABSOLUTE((field1-field2)/(MAXIMUM(field1, field2))) the problem is I dont know how to ask for maximum of 2 numbers. Since MAX in sql returns the max of column. ...
I have moved my drupal site from one mysql server to another one. Old Machine has 1 cpu, 1GB Ram New Machine has 4 cpu, 4GB Ram. I have a huge negative difference in perfomance on this query ( 2 mins vs 2 secs ) select distinct c.client from client_table c LEFT JOIN reps r on ( c.client = r.client ) where r.user_id is...
Hi I use mysql administrator and have restored backup files (backup.sql). I would like to use restore the structure without data and it is not giving me an option to do so. I understand phpadmin provides this. I can not use this however. Any one can tell me an easy way? ...
I am writing a web application, that is US specific, so the format that other countries use for postal codes are not important. I have a list of us zip codes that i am trying to load into a database table that includes the 5 digit us zip code latitude longitude usps classification code state code city the zip code is the primary ke...
More specifically I can't get the $last_name to display at all. In the following code: $name_and_date = "$first_name $last_name | $today | <a href=\"logout.php\">Log out</a>"; Here is more of the code. // Place Session variables into local variables $user_id = $_SESSION['user_id']; $first_name = $_SESSION['first_name']; $last_name =...
I have a commercial program that takes input from a delimited text file and stores that data in a MySQL database. I am writing a Perl script that essentially removes the need for the commercial program and uploads the data directly to the database. However, I am unsure in which tables the commercial program stores all the data. There ...
How do I parse a dash-delimited value in a SQL table and use the different parsed-out values to do lookups in other tables? Say I have users.nationality, which is a varchar like "41-33-11". I have the country, city and state tables keyed to those ids, 41 ("Buffalo"), 33 ("NY") and 11 ("USA"). SELECT users.name, users.email, users.nati...
What's the best way to select only those rows from the table that have been created in last 7 days? There are dozens of time and date functions in MySQL and I'm a little bit confused about what's the easiest way to do this. For the sake of this question, assume that you have a table called "my_table" and it contains a row "created_at" ...