mysql

MySQL Article Database Structure?

I wanted to create an article database for my users I was wondering how should my MySQL database structure look like? Here is how my database table structure look like so far. CREATE TABLE users_articles ( id INT UNSIGNED NOT NULL AUTO_INCREMENT, user_id INT UNSIGNED NOT NULL, PRIMARY KEY (id) ); ...

sphinx and one-to-many associations

The examples I've seen for sphinx search don't really use joins. If I had a schema like this (one book has multiple reviews), create table book ( id int auto_increment, title varchar(200), summary longtext ); create table reviews ( id int auto_increment, book_id int, review longtext ); What sort of query should I give to sphinx so tha...

How to count rows in MySqlDataReader?

Hi, I have successfully switched my project from odbc.datareader to mysql.datareader. The problem is that with the first one /odbc datareader), the AffectedRows property retrieves the number of rows correctly even when it was pure query. But it doesn work with mysql.datareader, its -1 then. So I cannot see the way how to retrieve the num...

Mysql trigger/events vs Cronjob

Hello, I have an auction website which let my users place an unlimited amount of autobiddings. To monitor these autobiddings something has to check the database every second. My question is if it is better to use mysql trigger events or to user a cronjob every minute that executes a 60 sec looping php script. If i use the mysql tr...

MySQL - length() vs char_length()

What's the main difference between length() and char_length()? I believe it has something to do with binary and non-binary strings. Is there any practical reason to store strings as binary? mysql> select length('MySQL'), char_length('MySQL'); +-----------------+----------------------+ | length('MySQL') | char_length('MySQL') | +------...

Aggregate functions - getting columns not included in the GROUP BY clause

I have a table which has three fields (item_id, source, and price). (item_id, source, and price) together make up the primary key. item_id source price ------- ------- ----- 1 paris 13 1 london 12 1 newyork 15 2 paris 22 2 london 15 2 newyork...

special characters wont be inserted into mysql table the right way...

I have a mysql database which Im trying to submit values to through a php file from a form. If I manually insert values to the table 'myTable' with special characters like the swedish åäö letters, it works fine. But when I am trying to use 'INSERT INTO' after a form is submitted on my page, to insert values to the table, all special ch...

PHP5 OOP Tutorial or examples

Hello, I am currently trying to get my head around OOP in PHP, I understand the basic concepts etc. Up to this I have been working for tutorials and books. What I am looking for is some good tutorials that use real examples [ie make a functioning application] or a simple well written application [with source code] so I can look through ...

would it be faster to implement php code into html, or to use only php? See more details...

Im currently using PHP to fetch results from a mysql db. Im also displaying the results by building a table and all with PHP also. My question is, would it improve loading speed if I would just call the php variables from a HTML document (PHP + HTML). Or maybe it doesn't matter, and I should go with the ONLY PHP solution that I alread...

2 mysql instances in MAC

i recently switched to mac. first and foremost i installed xampp. then for django-python-mysql connectivity, i "somehow" ended up installing a seperate MySQL. now the seperate mysql installation is active all the time and the Xampp one doesnt switch on unless i kill the other one. what i wanted to know is it possible to make xampp work ...

Can't connect to MySQL database from tomcat

Hey, I'm getting this error: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: Could not create connection to database server. Attempted reconnect 3 times. Giving up. I'm just trying to connect to the database. With this code <%@page import="java.sql.*"%> <% try{ // Class.forName("com.mysql.jdbc.Driver"); C...

MySQL: Select multiple tables

I want to select multiple tables with MySQL. But I don't know exactly the name of them, but they have all the same structure. For example: table_345: id || row_1 || row_2 || row_3 I want to have something like that: SELECT `id` FROM table_* ...

Web services Security

Hi I have a question regarding security, and web services. I need a web service to provide an interface for the underlying mySQL database. I am trying to get a Blackberry Application to store data on the web servers mySQL database through a web service. My question is, how can I ensure that the bb-application is the only thing tha...

many-to-many relationship INSERT question

i am trying to create a many to many relationship between 2 tables. ive got 3 tables for that. it follows TOXY model. table a: a.id (primary key) table ab: ab.a_id (foreign key) ab.b_id (foreign key) table b: b.id (primary key) how should i insert the data so it will be all linked up? like this? "INSERT INTO a ('name') VALUES ('my nam...

how to select n rows

i have a database about several companies which include their name, price of share, %age change: NAME PRICE % CHANGE ------------------------------------------------- a 67 11 b 23 6 c 456 5.89 d 112 ...

Mysql Text Storage?

I was wondering if you where to have an article or articles with huge amounts of text, what would be better when creating the database structure for the articles text? And why? What will be the advantages or disadvantages if any?. I was thinking of using one of the data types below to hold the articles text for the MySQL database. VA...

Ö (O with double dots) in Mysql record?

Hi, I have a row in a MySQL table with "ö". The whole word is "företag". How do I select that word in a sql query? I've tried with fretag but it didn't work. The table is using utf8. ...

How to group by week in mysql?

Oracle's table server offers a built in function TRUNC(timestamp,'DY'). This function converts any timestamp to midnight on the previous Sunday. What's the best way to do this in mySQL? Oracle also offers TRUNC(timestamp,'MM') to convert a timestamp to midnight on the first day of the month in which it occurs. In MySQL, this one is st...

MySQL NULL or NOT NULL That is The Question?

What is the difference between NULL and NOT NULL? And when should they be used? ...

wild expressions in mysql IN statement

i want to use a WILDCARD for everything in the statement beneath: "SELECT threads.* FROM thread_tag_map, threads, tags WHERE thread_tag_map.thread_id = threads.id AND thread_tag_map.tag_id = tags.id AND (tags.name IN ('WILDCARD'))"; What should I replace the WILDCARD with to be able to select every entry? I have tried...