[How do I select all records where something starts with an A]

If he does that Jon, he will get everything with $vn in - starting, middle or ending.

 

He wants:

 

LIKE \'$vn%\'

[Closing the MySQL connection]

Good job.

 

I usually include a mysql_close() footer anyway, so I can add other footer information along with it (html anchors etc.).

[Selecting last replied to topics in a forum]

Then why do you have these lines in your create script?

 

  forumno int(8) unsigned NOT NULL default \'0\', 

 threadid int(8) unsigned NOT NULL default \'0\', 

 

There are 3 distinct entities in your table: forum, thread and post. What happens when the only post in a thread is deleted? That thread dissappears. What happens when the only post in a forum is deleted? That forum disappears.

 

You need to normalise your tables my friend.

[Unable to update MYSQL database]

You\'re welcome. Well done.

 

What was missing?

[Selecting last replied to topics in a forum]

Nah, looks great.

 

SELECT thread.threadID, Max(post.postID) AS MaxOfpostID

FROM post WHERE post.threadID = thread.threadID

GROUP BY thread.threadID

ORDER BY Max(post.postID) DESC;

 

[Help designing database]

Looks sweet, but shouldn\'t profileID be a FK in access (since access seems to be the user table)?

 

That way a user has a profile, as opposed to a profile having a user?

[Closing the MySQL connection]

What you suggest is very possible and good practice.

I can confirm that including connection and closing statements works well.

 

I am only just now assessing the impact of user sessions and open connections.

 

I have had a look at a PHP reference book and cannot find any way of testing the connection, yet.

[Unable to update MYSQL database]

One question, why are you using replace, why not update? I think that would be easier and less confusing.

[Unable to update MYSQL database]

REPLACE does exactly the same thing as INSERT INTO (even has the same syntax) except it replaces the old data instead of adding a new line.

 

You tell the DBMS what fields you are replacing then you need to specify which row you are replacing data with in the REPLACE clause, so:

 

REPLACE INTO table (field1, field2, field3)

SELECT field1, field2, field3 FROM table WHERE tableID=$tableID

 

In the case of an on-the-fly operation, use:

 

REPLACE INTO table (fieldID, field2, field3)

VALUES ($fieldID, $field2, $field3)

 

Is that better?

[Help designing database]

You\'re welcome.

 

Ah! Yes, you have answered your own question.

 

I would say it is dependent on the size of your database. If small, you may as well keep the rule profile inside each user(?) to save query time (and since you are coding default values into a table anyway). However, it is good practice to normalise your tables (especially if the DB is larger), therefore the JOIN table is more sensible.

[Selecting last replied to topics in a forum]

What is your DB structure?

 

forum (forumID)

thread (threadID, forumID)

post (postID,threadID)

 

??

[Unable to update MYSQL database]

You need a SELECT in your REPLACE statement or do the VALUES as a line:

 

1. REPLACE INTO table ()

VALUES ();

 

2. REPLACE INTO table ()

SELECT select_clause;

[help with query]

Heheh.

 

He\'s gonna have a bitch of a time with this.

 

He has nowhere to link teamID. He has home team and away team in an un-normalised table you cannot use OR statements on a JOIN (where teamID = hometeam or teamID = awayteam.....).

 

3 options:

 

1. Get MySQL 4 so you can do UNIONs

 

2. Do 2 queries that INSERT INTO a temp table on hometeam, then awayteam, then SELECT from the temp table.

 

3. Normalise your tables. Add table team_match (team_matchID, hometeam, awayteam) then do complex queries linking team, match and team_match.

[Help designing database]

1.You need to link your tables with question_person, yes.

 

Create table question (questionID, question, etc.)

Create table person (personID etc.)

Create table question_person (questionID, personID, answer, etc.)

 

2.In the create table statements use DEFAULT value for each data item you want to set a default for - so in this case:

 

...

answer int(1) DEFAULT 0,

...

 

3.I know you say you want to create every question for every person with a default value of \'0\' but that defeats the purpose of creating question_person. If you follow step one a new person will appear in question_person when you code it so that they answer questions - only then will they receive a question_person.questionID and be given a default \'0\' for their answer. It sounds like you want to create a un-normalised table in person that codes for NULL values (or default \'0\') (that\'s bad).

 

Why do you need to see default \'0\' for each question? Surely its better to leave a table unfilled until someone answers a question?

[Books/Question]

Just go to B&N and seach for \"mysql\".

 

The Dubois 4th edition is great, I have it and it will serve all your MySQL needs.

 

The O\'Reilly one is good if you want something that combines the MySQL with PHP.

 

You have to watch out for the books - some have all MySQL and a little PHP, some have all PHP with a little MySQL and some mix the two. The SAMS books are OK though you can do better for $$$ than B&N.

[3 tables, 1 query]

No, the order of the WHERE clauses does not matter.

 

What are you passing to mysql_num_rows?

 

I just tested your syntax with one of my a-b-c tables and it worked.

[What's Database Normalization ?]

IIRC, doesn\'t a relational RDBMS only have to satisfy Boyce Codd NF to be termed \"relational\".

 

This is useful stuff for \"noobs\" - maybe you should consider doing more or linking to database theory resources. I find that 90% of peoples\' problems aren\'t with code and syntax but with not understanding basic database operations.

[3 tables, 1 query]

Looks good. You only need 1 articleID in the SELECT clause BTW (not that it matters, but its efficient syntax).

[MySQL is Bi-Polar?]

Is all the data from this script going in to the recently deleted/recreated table?

[3 tables, 1 query]

You have only joined a to c.

 

You need to join b to a or c.

[Error 2003:Can't connect to MySql server on 'localhost']

Do you have the MySQL server set up to start automatically when your PC starts?

 

If the server wasn\'t running, you\'d get that error. Maybe the server didn\'t load or maybe it closed and you didn\'t noticed.

 

If in doubt, restart. :wink:

[Extremely weird SQL behavior]

Is this error the reason you chose \"Dark-Hawk\" as your user name??

[relational table]

That\'s essentially how the tblUserProject works.

 

Consider this

 

SELECT * FROM tbluser, tblProject, tblUserProject GROUP BY  projectID

 

You\'d get:

 

Project 1

_______

User1

User2

User3

 

Project 2

_______

User1

 

Project 3

_______

User2

User3

 

etc.

 

Your table of ticks and crosses is not normalised - it is coding for nulls.

 

You could output the GROUP BY query into an HTML table:

 

[Project 1][Project 2]

User 1 User1

User2 User 3

 

How about that?[/code]

[relational table]

So you have tblUser(userID), tblProject(projectID) and tblUserProject (userProjectID, userID, projectID)?

 

To get a user\'s projects you use this query:

 

"SELECT * FROM tblUser, tblProject, tblUserProject

WHERE tblUser.userID = tblUserProject.userID

AND tblProject.projectID = tblUserProject.projectID

AND user = $user";

 

And that\'s not the only way - you baically need to JOIN the tables because of the many-to-many relationships. There are simple guides to SQL that will teach you these answers.

[storing and retriving paths - help wanted]

You need to output the HTML required to display the image path too...

 

So for each instance of your loop, you must output:

 

echo "<img src = "";

echo $row["imagepath"];

echo "">";

 

Just as you would to display other HTML - data in table cells etc.