mysql

Getting mySQL date_format to display in PHP

Hi, This could sound like a very simple question to many of you, but I seem to be having trouble getting a basic date_format to work with my mySQL statement and then to be displayed using php. Here is the code that I currently have: $result = mysql_query("SELECT *, DATE_FORMAT('timestamp', '%W %D %M %Y') as date FROM articleDB WHERE ...

Making a MySQL view easier to update & load

I'm having a view that's actually a combination of 2 other views, which in they turn are the split of Table1 join Table2. The split in the 2 view is based on whether the column of the table1 EnterDate=curDate(), which determines the way a new column is calculated ( NewColumn=(Table2.Cpx-Table1.Px)*Table1.Size if EnterDate=curdate() and N...

Help with this JOIN

$threads = mysql_query("SELECT DISTINCT t.id, t.title FROM threads t LEFT JOIN comments c ON t.id = c.parentID ORDER BY c.date DESC"); while ($thread = mysql_fetch_assoc($threads)) { echo "<li><a href=\"?threadID=$thread[id]\">".htmlspecialchars($thread['title'])."</a></li>\n"; } Can ...

Finding Zip Codes in a Specific Radius

I have found this popular PHP/MySQL Script called Zip Location by SaniSoft and it works great besides one thing: It doesn't in some instances. It seems that any radius under 20 miles returns the same amount of zip codes as 20 miles. I have searched all over google, but to no avail and I was wondering if someone had some insight on this ...

To use a foreign key in MySQL or not to?

I have been coding with MySQL DBs for a couple years now and I have never used a foreign key. Now, I understand that a foreign key is like an ID on one table that corresponds with a primary key of another table. I have a user table on my site and probably around 10 other tables that all correspond with the primary key of my user table ...

mysqli using prepare

Hello guys. I need some help, I have created this function and it works fine, but now I need to use "prepare" in MySQLi. Can I get some help? I have tried http://dk.php.net/manual/en/mysqli.prepare.php but I don't really understand how to prepare work with MySQLi. My db obj is ( $this->db ) in my class. I need a sample of my own cod...

SQL Alternative to performing an INNER JOIN on a single table

I have a large table (TokenFrequency) which has millions of rows in it. The TokenFrequency table that is structured like this: Table - TokenFrequency id - int, primary key source - int, foreign key token - char count - int My goal is to select all of the rows in which two sources have the same token in it. For example if my table ...

Debugging a MySQL charset error

I have a project where I scrape some data from a HTML file, put it into a MySQL database and then read it out again and display it to the user. Whenever the servlet is inserting something into the DB the "£" sign is being inserted as a � and when read out again presented as that. The application is written in Java (with Spring) and JPA ...

Php or mysql commands - data from mysql row into text file

How do I get all data from one row from mysql table exported into text files, but formatted this way: one field under another, one per line i would like to break that data into pieces and save for example 50 lines in file1.txt then next 50 in file2.txt and so on until end (it's not round number so last file would have less lines proba...

How should I handle entries when the user is deleted?

I run a forum which I built myself. Ok so all users have the opportunity to delete themselves. But all their threads and posts will remain. But right now where it should say their username it's just blank. How should I handle this? Should I make a new user and call it e.g. "deleted user" and assign all threads/posts to that ID when t...

html special characters into outfile.txt

I've got a database where for efficiency, i've put the data into the db in html encoded formats. I do maintenance on the data, and then move it into production via an 'into outfile', so it ends up in a text file. The special characters don't make it across cleanly, and it comes out as all messed up code. Is there a way to maintain t...

mysql multiple queries with php

I am trying to have a search result for someones name exploded then the first name and last name each search against each field 'first' and 'last'. But need to leave room what if someone search for first last midle name like = $who = James David Smith it will exploded it and search each of the names against the first column and the last...

Is MySQL appropriate for a read-heavy database with 3.5m+ rows? If so, which engine?

My experience with databases is with fairly small web applications, but now I'm working with a dataset of voter information for an entire state. There are approximately 3.5m voters and I will need to do quite a bit of reporting on them based on their address, voting history, age, etc. The web application itself will be written with Djang...

How can I split a mysql table into multiple tables and query the correct arrea?

Below is my friend table, I included 2 entries to show how it works, When a user adds a person as a friend it inserts 2 entries into the DB with this code; <?PHP //status 0=approved 1=declined approval 3=pending approval $sql = "insert into friend_friend (userid,friendid,status,submit_date) values ('$_SESSION[auto_id]','$frie...

Configuring MySQL using command prompt and MySQLInstanceConfig.exe

I am trying to install and configure MySQL (version 4.1.22 on Windows XP) using the command prompt silently so it can be included within an installer for a different application. I thought I had everything installed and configured correctly but the MySQL service fails to start, even if I try to start it through the services snap-in. I a...

php mysql gaming levelup script issues

Here is my problem. In my game I am trying to implement this leveling system where once you reached a certain amount of experience that you advance to a higher level. I am using php 5.0 and the Latest MYSQL. My problem is that i cannot get my scripts to execute properly. SO here are some dummy stats followed by my code Any information t...

mysql is not running but lock exists

Hi All, I am getting "mysql is not running but lock exists" this error when I am checking the status of mysql server. I have removed the lock files using following command : rm /var/lock/subsys/mysql but still I am getting same error. Can anyone provide any input on this. Thanks in advance. ...

Php - How to exclude data from mysql.

Hi folks, How to exclude some of countries from List Menu at below? Now my code will list all of country name from database. Example I want to exclude Albania country from the List Menu. How to implement it according to these code. Code <?php $select_country=mysql_query("SELECT * FROM tbl_country ORDER BY country_name ASC")?> <se...

How do I search part of a column?

I have a mysql table containing 40 million records that is being populated by a process over which I have no control. Data is added only once every month. This table needs to be search-able by the Name column. But the name column contains the full name in the format 'Last First Middle'. In the sphinx.conf, I have sql_query = SELECT Id...

How to make my mysql queries for a friend feed on a large DB?

Here is my mysql table scheme; status table // has statusID, userID,date, subject, date comment table // holds commentID, userID who posted, date, and statusID that it belongs too user table //holds userID username and user photo URL With that table scheme above, I need to do this; Fast with least amount of DB queries Show all stat...