MYSQL :: Properly Querying A Relational Db.

UPDATE Will Not Add Properly
I have an online comic engine I'm working on, and I just noticed that, as opposed to adding one view (like it should) it will always add two. Can anyone help me out with this? I've already determined it's just this line of code, and that it's not being called twice.

$query = "UPDATE `".SQL_COMICS."` SET comic_views = (comic_views + 1) WHERE comic_id = '".$comic_id."' LIMIT 1";
mysql_query($query) or die(mysql_die_message());
echo($query);

Getting Utf8 To Work Properly
I have made a few tables in a database, and I'm trying to get my Japanese text to display properly. I have the collation set to utf8_general_ci, and I even added the mysql_query("SET NAMES 'UTF8'"); code into the php page. But even with all of that in, it still won't display properly. The characters show up as ã‚»ã‚*ãƒã‚¯ã«ã‚“ã˜ã‚ƒãŸã„ã‘ã¤ï¼ and other junk like that.
Now this was working before until I added a second mySQL connection into the page, which inputs the second table. I'm not sure if you can make 2 connections on the same page, but I did and it pretty much messed everything up. I took the second table away, and things were still messed up.
My question is how can I get the Japanese text to display properly? It does display properly on the phpMyAdmin page, but I get garbage on the actual page.

How To Install Mydsl Properly
is there a step by step guide for installing mydsl properly and creating a databse?

Search Does Not Match Properly
i had created a search function on a web application.

the problem is, i try enter some of info to it, certain text can be found but certain text can be match although they are in database.

this my query:

$keywordu = strupper($keyword);
$keywordl = strlower($keyword);

$sql = "SELECT E.* FROM employes AS E, dvi_problem AS DV WHERE AS.empId = DV.empId AND (E.emp_name LIKE '%".$keyWordl."%' OR DV.reference LIKE '".$keywordi."''%".$keywordl."%' OR E.emp_name = '%".$keywordu."%' OR DV.reference LIKE '%".$keywordu."%')"

$query = mysql_query($sql, $db) or die('Fail to search.');

i test with direct copy the data from database, some string i copy can search it but some other string i copy direct it can work wo...

i try show out the variable $sql value, it show with not error, the keywordu n keywordl aslo show out the value when i print out the $sql variable.

Mysqld Is Not Running Properly
I have installed mysql server & client version 3.23.54a from Redhat installation 9.0. I could install the RPM 's with out any problem.

I can see 'mysqld' program . But when I run it shows a message like mysqld started [ok].When I checked 'ps aux' , I could not see any process running with mysqld. Also I could not run any programs like mysqladmin etc. All of them fail. does anybody know what is the mistake ? What should I do ? Also when I try to stop mysqld it fails.

Designing Databases Properly
After what seems like far too much agonizing, I think I've decided to build my own php "shopping cart" and hack my own interface to paypal IPN, because all the other solutions I've come across are massively oversized and overdeveloped for my needs.

But I could use some help in setting up the structure. I'm not sure of the best way to handle "options" with my various products.

First of course I set up a basic database with product fields like:
sku, price, name, etc. etc.

The tricky part is that some products will have special options, some dropdowns and some text fields, and I know I won't be able to predict all these in advance, like:
item color, accessory color, font, initial, initials ...

In addition, some products will call for extra product photos and others won't ...

And then I will need some way of storing all these extra options for a given customer's order when it comes through.

Relational Db
i have a table "events" and a table " dates". As you may guess, one event has many dates (one-to-many relationship).
i would like to return a table that contains one event per row, with one column containing that event's dates.
target mysql version is mysql 3.23.58
Is it at all possible? I think mysql 4.1 has inner select but it is not possible in this case.

Extended-insert Not Working Properly
I am having a problem with using the --extended-insert option when using mysqldump. As I understand it, this option creates multiple insert statements within the .sql file for tables that contain more than 1 row of data.

According to documentation I have found, this substantially speeds up the restoration process. The main database I am concerned with uses the InnoDB engine, however I also want to backup the mysql database as well (which, to my knowledge, uses the MyISAM engine).

However, when I execute the database dump using this option, there is only 1 insert statement, and all values are seperated by commas. When trying to restore my databases, this causes my system to eventually freeze up due to the inefficiency of inserting multiple rows this way. I am using Windows XP, 1.8 Ghz, 512 MB of RAM.

The command I am issuing is as follows:

mysqldump --opt --single-transaction --u root --all-databases > "C: estbackup.sql"

I have also issued the following command, with the same result:

mysqldump --extended-insert --single-transaction --u root --all-databases > "C: estbackup.sql"

Properly Re-starting Replication Process
I've just tried to re-start a replication process and the slave's
tables came up marked as crashed. I'm doing a repair on one of them but
there are a lot of databases/tables so I can't check them all (or is
there a table entry somewhere that flags crashed tables?) Here is the
sequence I used: Code:

Storing Large Integers Properly
I am storing book isbn numbers in a table. isbn numbers are 10 digit
numbers and many start with 0. The data type of the field I am storing
this info in is a bigint(16) unsigned.

It appears that isbns that start
with 0 are going in as 9 digit numbers, the 0 is being ignored or
stripped. I have experienced this before with integer data types
ignoring leading 0s. I'm wondering how to address this? Should I
change the field to a varchar or char data type?

Two Digit Numbers Not Formatting Properly
I have a table I am using to populate drop-down menus. It is for languages.
each language has a specific code that must be saved to the field in question.They work fine except for two codes: 00 and 01.
I can't change the codes to anything else. They have to be 00 and 01. THey are saving to the spreadsheet as 0 and 1 however.

Inserting Dates Properly Formatted
I have a dataset that has all it's date formatted as MM/DD/YYYY. I want to be able to insert this into a table that is using a field set as 'date'.
How would I go about properly formatting the MM/DD/YYYY to insert into the table?
Right now when I insert I end up with all the dates storing wrong. IE: 9/6/2005 stores as 2009-06-20.

Relational Tables
i'm working on a project that consists of 2 things.
first a info-website: some general info and news items, projects, etc...
second an archive: everything about past projects

i want to structure the data into an mysql database and after reading this article , i've got a general idea how to build up a good database.

http://dev.mysql.com/tech-resources...malization.html

the only thing I'm wondering. how far must you go, i can almost split up all info into seperat tables and link them together. is it maybe a good idea to make 2 or 3 databases with less tables and make links between databases

for instence: adresses. you can make a table with adresses and relational tabels with al the zip codes, city, etc... if i do this with all data for the projects of de site, i get a database with a countless amount of tables. is it then wise to setup an extra database with just all the adress info and thuss less tables in each database?

Relational Databases
i want to set up a relational database in mysql. i want one table (items) to have a field (supplier) which gathers information fromt the table (people) field (supplier). is this possible?

Relational DB Design
I've contructed a fairly simple normalized (3NF), relational database. By simple, I mean each table relates to other table(s) in a 1 to many (1,n) relationship. Now, I am trying to extend this database, but I'm not sure how to proceed given specific qualifiers. Allow me to illustrate:

* 1 Pod has 4 Channels > (1,4) relationship.

* 1 Sensor may occupy 0, 1 or 2 Channels > (0|1|2) relationship.

* 1 Pod has between 0 and 4 Sensors > Indirect relationship between Pod and Sensors. Direct relationship is between Channels and Sensors.

* 1 Sensor connects to 0 or more Pods, but only 1 Pod at any given time. Indirect Many-to-many relationship between Pod and Sensor. Again, the direct relationship is between Channels and Sensors.

I've drawn up a db diagram and attached. PodSernsorAssociation allows a many to many relationship between Pod and Sensor, although it may not be correct to even have this relationship.

Relational Database
I need help with creating a relational database and normalizing it.
I've been studying the process of creating a relational database,
however I am unsure if I am doing it correctly. Below is a sample
list I have created (on paper)of my database design.

Can someone review and let me know if I am doing everything correctly
and give me suggestions for improvement? Code:

Relational Tables
I come from a strong MSSQL background. I recently acquired a project that I need to do with MySQL. In MSSQL I was able to link the tables together, not allowing data to be deleted that was linked into another table. How can I force that type of realtionship on tables in MySQL?

Many-Many Vs Object Relational
In the process of trying to figure this thing out, I've been
doing the old "stand around in the store and read as much as possible
before you look like a derelict" thing. This time, with the O'Reilly
"Managing and Using SQL" (I think that's it).

Anyway, I was looking at a chapter discussing database design (without
any direct reference to impementations in MySQL), and they mention
Obj/Relationaldesign as a way to have a many-to-many relationship. I'm
wondering if they're trying to imply that there are different ways to
implement a many to many. In my bkgnd, I would assume that in any
many-to-many you have to have the joining table, and there's not much
else to do but go to it and select all the rows that have the key
you're interested in.

Relational Database
i want to make a relation between two table, if you delete a specified record in the first table, the record in the second table that related to the first will be deleted too.

eg. if i have a table contains a studentid field and some information about that student and the second table contains the studentid too, and the grades of that student, if i want to delete this student all his grades will be deleted.
how to do that?

Relational Database
I'm redesigning my company website and I've come across a problem that I'm hoping some of you will be able to help me with.

I want to have a "customer login" section of the site. When a customer logs in they can view three categories of files that pertain to that customer. They are Drawings, Manuals and Software. I will be uploading the drawings/manuals and software to the website and so I assume that the table will have to hold the 'link' to these uploads.

I need to design a database that will allow customers to access multiple files but only the files that pertain to that customer.

I am thinking that I'll need two tables, one of customers log in details and one that holds the file names of the relevant drawings, manuals and software that they are able to download.

The problem is that I want multiple customer log ins to be able to see the same files. Basically like a 'permissions' thing. I'm not sure on the best way to do this in a database and I wondered if anyone has any ideas?

Relational DB With MySQL
Is there a way to create relational databases in MySQL like you can in MS SQL? I primarily use MySQL, and I have started to use some MSSQL stuff because I wanted to work on relational dbs, but I just didn't know if there was a way to do it in MySQL like MSSQL

Mysqlcheck: Clients Are Using Or Haven't Closed The Table Properly
I'm using the command mysqlcheck --all-databases --auto-repair --silent
and I see these warnings (listed below) on a daily occurrence. Is mysqlcheck
causing these warnings? I can't see how a table can close inappropriately.
How can I prevent this.

Hoping to here from MySQL users who have came across this. I'm using MySQL
4.1.11 on RHEL. Code:

Getting Text Retrieved From Mysql To Fit Tables Properly
I have been working on a really simple mysql/php set of pages.

Page 1: Input 3 peices of data (Title, Date and Text)

Page 2: Display all news that has been inputted

I have it working, except for one problem. My website is built with html, and tables. I want it so that when the text gets to the edge of the table, it drops down a line and starts again on the next line - so it doesn't carry on and on.

Theres no problem with title and date - they're just a few words, but the text part of the news is usually 5 or 6 lines long. This means that it wont all fit on one line without ruining the entire website display. Code:

MySQL Relational Database
Anyone here has a suggestions on what to do on this tasks:

1. I want to make a form which has 30 or more fields that our agents will fill in. These are Personal Info, Address and Housing Info, Employment Info, Security Info and Additiona cards info. Now as of the moment, These five categories are being saved in a one table for the query purposes that it is easy to fetch up (i'm a newbie and don't know how to segregate from one to five tables). I'm planning to resetup the database and segregate these five categories into 5 tables also. Now, my question is: these five categories are in one form or page. In a page the format of my web is on below. Now, if agents will click SUBMIT button of course all data will be encoded to the database on each corresponding table. But how I would be able to get or query those data in a one form. If it needs a lookup table, how would I implement that one.

Please help me.. really need your inputs on this matter. Thank you so much....

I. Personal Info:
Firstname: __________ Middlename:_________ Lastname: _______
Homephone:
email address:

II. Address info and house info:
street:_________ city:_______ state:_______ zip:_______
House status:________ House type:____________

III. Employer info:
name:_______________ occupation:___________
salary: ________________
IV. Security info:
Date of Birth:_________ SSN:_________ Mother's Maiden Name:_______

V. Additional Cards Info:
Firstname:________ Middlename:_________ Lastname:_________

(SUBMIT Button)

MySQL Relational Schema
I have a large mysql database and would like to see a graphical representation of the database schema i.e. primary keys, foreign keys, related tables.

Does anyone know of a good development tool which will display this?

Relational Query And JOINs
I'm trying to set up a simple invoicing system, and am having trouble figuring out the right query to retrieve data from three tables...

The important fields in the tables are:

Customers: CustomerID, Name, Address etc etc
Orders: OrderID, CustomerID, PaidSoFar
OrderRows: OrderID, ProductID, RowTotal

I need to make a list looking something like this:

M2M Relational Query Help Needed
I have 3 tables, below are a list of the significant fields in each.

Stories
* story_id
* story_date

Editions
* edition_id
* publication_id

Story_Edition_Jct
* story_id
* edition_id

What I need to do is create a list of all the stories that are newer than 30 days and have not been assigned to a given publication_id, but may or may not have been assigned an edition.

Just to clear that up some more, a publication consists of many editions, so we're looking for stories that may have been published in other publications, but want to restrict them from being republished in a given publication.

The following query is what I've come up with:

Checking Relational Db Structure
I have a table with the following id, username, password, email, phone, etc. What I want to do is have multiple pages of jobs where these people can submit themselves to. I was thinking of adding 2 more tables. One with jobs like this j_id, j_desc.

Then a second for job submits from users in the main table like this s_id, sj_id. Where the s_id is relative to the id, and j_id is relative to the sj_id. Will this work? Also is there someway of preventing double submits?

Relational Model Software
I need to build the relational model of a database I already have. Does anybody know if there would be a software or so that would make it for me withou me having to rebuild the database? I found some software like CaseStudio, but i'd like somthing simpler, that would import the SQL script or something like this.

Relational Database, Normalized, Need Query
So I am working on a project that deals with a large normalized database. The database used to be constructed with one users table that had a location field which contained a comma separated string of location abbreviations signifying which location that employee worked at. However, I have now changed the database to as follows:

CREATE TABLE `location` (
`LocationID` smallint(6) NOT NULL auto_increment,
`LocationName` varchar(120) NOT NULL,
PRIMARY KEY (`LocationID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

CREATE TABLE `users` (
`UserID` int(11) NOT NULL auto_increment,
`fname` varchar(255) NOT NULL,
`minitial` varchar(2) NOT NULL,
`lname` varchar(255) NOT NULL,
`department` mediumtext NOT NULL,
`positionTitle` varchar(255) NOT NULL,
`supervisor` varchar(4) NOT NULL,
PRIMARY KEY (`UserID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=229 ;

CREATE TABLE `locationrelationship` (
`LocRelationID` int(11) NOT NULL auto_increment,
`UserID` int(11) NOT NULL,
`LocationID` int(11) NOT NULL,
PRIMARY KEY (`LocRelationID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=10 ;
There are a lot more fields in the users table but the above should do.

My problem is I am now trying to query the users table and include the location name that the employee works at.

The below query should do this if the employee only has one location otherwise it will display multiple of the same user:

SELECT replace(concat_ws(' ', users.fname, null, users.minitial, null, users.lname), ' ', ' ') AS userName, users.department, location.LocationName FROM users JOIN locationrelationship ON users.UserID = locationrelationship.UserID JOIN location ON location.LocationID = locationrelationship.LocationID
An example of this could be:

John Doe, Billing, South Bend
John Doe, Billing, Elkhart
Jane Smith, Sales, South Bend
However I am trying to create a query that joins multiple locations and separate them by a comma then a space then applies it to some locations value in a query.

An example of what I am looking to do is:

John Doe, Billing, "South Bend, Elkhart"
Jane Smith, Sales, South Bend
Any ideas?

Making My Relational Tables Relate
I have created a mysql database and it is normalised to 3rd form.

I can successfully join some of my tables to give the results I am looking for.

However other tables that I join give the wrong data.

I created the table data in a spreadsheet and simply filled in the numbers where table_id foreign key corresponded to table_id primary key in the primary table.

This is probably why some tables relate correctly and others (where the data is repetitive and a reference table was created) do not.

Obviously it appears this is not the way to create relational tables?

Many To Many Querying
I'm working on a site that will showcase graphic design projects. Every project can have tags. Right now, I've got the following:

tag_type:
tag_type_id int,
name varchar(256)

tag:
tag_id int,
project_id int,
tag_type_id int

project:
project_id int,
name varchar(256),
text text

So now, I'd like to know what the best way would be to extract the projects and their respective tags. I'm programming this in PHP. So basically what I'd want in the end is to be able to show all projects, and all tags that belong to each project. I'm not very experienced in using MySQL. All I can think of right now is to loop through the projects and do a new query every time, but that seems very inefficient to me.

Querying From Two Databases
I need to query from two servers at once [in the same script], both databases are on the same server [localhost] and use the same username & password. How can this be done [php]?

Querying Two Tables
I have one table filled with a list of events. fields include date, title, and location of the event.

My other table has a list of locations and their map url.

i want to structure a query to include date, title, and location of each event. if the event location is in the map location table, i want the map url, too.

SELECT events.date, events.title, events.location,
maps.url
FROM events, maps
WHERE events.date >= CURDATE() AND maps.location=events.location
ORDER BY events.date ASC

this query returns only events that have a map. how do i get all events, and the url, if they have one?

Querying From Different Machines
I have databases on two different machines which I want to select from in one query, any good way of doing this without moving tables from one machine to another?

Querying Two Tables
A customer places an order.

A PHP script opens dbMyDatabase, queries table tbl_customers and if the customer is new, his details are inserted. Then order details are inserted into table tbl_orders, but this insert (tbl_orders) always fails.

Querying First 3 Letters
Can anybody tell me how to query the 3 order letters..

example is my code..

SELECT firstname, lastname, town, availability, expected_salary, keyword,dob,vacancy_type_id FROM rec_candidates WHERE keyword LIKE '%asp.net%';

Querying Database
i am making a php script where first it inserts a new row into the table, then a couple lines down it needs to query the table to find the same row that was just made. one of the columns is the datetime of when the entry was made, if i query the database a couple lines down for where the datetime was created ( NOW() ) would it return the same value or would the fact theres a few miliseconds difference change anything?

Querying The Database
I was wondering if there is a way to query mysql and get the next number that a calume is going to be if the colume is auto_increased by mysql.

Querying Timestamp
i got a timestamp timestamp(14) column.
format looks like 20050426043734
how do you query records created lets say within a week ago?

i tried

select * from table where timestamp < "one week ago";

but that doesnt seem to have worked.
i tried looking through mysql website but couldnt find an example @_@
i didnt want to do it via mathematical deductions...ie.. timestamp - 7
since im not sure how it'll handle month changes..i thought they would have like a built in statements like one week, one month..etc.

GUI For Populatin, Upating, And Querying
can anyone recommend a good GUI interface for mySQL?

Do you mostly use a command line client?

Querying Multiple Tables
I need to query multiple (15) tables to build a data entry screen. Is better to create 15 separate SELECT statements and query each table independently or create 1 large SELECT and query one time?

Querying A Category And Subcategory
I have a table in a database (store_category) that lists categories and subcategories. The subcategories have a field with the parent category's id in it. So something like:

id | name | parent
1 Dog 0
2 Puppy 1
3 Cat 0
4 Kitty 3

I then have a products table and in that table, each row has a field for the id of the category it's in.

The problem I'm having is selecting something like all of the products that are in both the Dog category and it's subcategories (in this case, Puppy). I can select a single category (parent or sub) just fine, but it's if I select only the parent and want to also include the products in it's subcategory that I run in to issues.

Querying A Column With Brackets In Its Name
I'm working with a poorly writtend database that has some
fields named as such: MTIC_PROD_VEND[ 1]

I do not have the option to rename this field but I do need to retreive data
from it with SQL. I've tried a suggestion of [MTIC_PROD_VEND[ 1]]] and that
doesn't seem to work, it may be because the 1 has a space leading it. Does
anyone happen to have any suggestion to try to work around this?

Querying Dates In Mysql
If I want to extract data with todays date then it's 'where blab blah =
current_date()'

For yesterday it's 'where blah blah = current_date()-1'

But can anyone tell me how to extract all data from the start of the
current week? Is there any way in Mysql to work out the start of the
current week?

The current month?

Year?

Querying Other Database From MySQL
We're using a mySQL database as a replica of another (Sybase) DB for
reporting purposes. The Sybase is part of a real-time mission critical
system - hence the separate database where people can run their queries
on.

Currently a separate application is copying the information we're
interested in from Sybase to mySQL in an overnight batch-process. We
want to extend this to loading every 15 minutes and are now
investigating the possible solutions.

I am considering to create a scheduled task in mySQL, where mySQL
itself reaches out to the Sybase ASE server and fetches the data. That
seems to make the setup much easier and eliminates the need for an
intermediate application.

Does anyone know if it is possible to create a "pass-through" query in
a mySQL stored procedure (or scheduled task)? I searched for this but
could not find anything, unfortunately.

Querying The Same Table Twice In The Same Query?
I have a table that consists of: -

ModuleID Int
PortID VARChar
Quantity Int

It is a linking table which links an individual module which may have ports of different types (or not) to a description of the ports. So for example a module might be: -

Module 1 has 4x(10/100 RJ45 Ports) and also 4x(MTRJ ports.)
Module 2 has 4x(10/100 RJ45 ports) and also 2x(SC ports).
Module 3 has 8x(10/100 RJ45 ports)

so the entries in the table would look something like: -