Ive been trying to teach myself web development stuff since I missed a bunch of classes hurting my back.

At this point Im writing a blog page that stores/retrieves from a mysql database.

I am completely new to doing any work with databases like this being that my databases class mostly focuses on theory with actual work being done in access (other than SQL/RA queries).

So I Ive already created the table, but now I have to go back in and assign an attribute to be the primary key.

How do you do this? Ive looked and read through mysql.com but Ive had no luck.

Anyone know?

12/7/2008 7:57:27 PM

ALTER TABLE table ADD PRIMARY KEY (column);

also, what kind of database class teaches ms access... that's a fucking joke...

12/7/2008 8:04:41 PM

Eh the way they have it set up here is that "databases 1" teaches a LOT of theory (like fucking normalization which is giving me a lot of problems as well), and the "exercises" are done in Access. Toward the end of the semester they move to SQL syntax by teaching SQL and RA queries, SQL syntax, but not creating DB's in SQL. Its odd because my web development class teaches more SQL stuff than my actual databases class.

Levels 2 and up (DB classes) are all SQL. Being that "DB 1" is required for all majors in the school (keep in mind we have a LOT of librarians - otherwise non-technical people) for them to understand the fundamentals of the purpose of a DB and how it relationally associates data, access is probably the best choice. For "real" database classes, or those who want to specialize in databases 2 and up are for them. Then again the CS department has their own classes on databases so it really all depends on the goals of the class in the respective department.


Thanks for the help too!

One more question though, how do I add the "auto_increment" to an attribute? I assume its the same method you just put it in place of "PK"?

Fuck it, I blew the table away and recreated it. It didnt have any data anyways.


[Edited on December 7, 2008 at 9:00 PM. Reason : .]

12/7/2008 8:38:35 PM

lol yeah that's something you normally do in your create table statement

12/7/2008 11:14:03 PM

http://www.phpmyadmin.net/home_page/index.php

use that

fuck doing things manually

12/8/2008 7:22:37 AM

yeah, no kidding.....
I would learn SQL for data manipulation - SELECTs, JOINs, ADD, etc. But for setting up the structure of a DB? phpmyadmin all the way. And the great thing about phpmyadmin is that it will show you the SQL statements that are executed for each command you do

12/8/2008 9:52:28 AM

Interfaces like that are nice; but honestly, if you can't do it manually, you should probably get someone more qualified to setup your table schema... or at least have them take a second look at your table schema so you don't do something stupid in your design which would be a costly fix later on.

12/8/2008 10:22:46 AM

Quote :

"if you can't do it manually, you should probably get someone more qualified to setup your table schema"

12/8/2008 10:30:07 AM

I dont have the option of using myphpadmin in a web development class.

12/8/2008 10:38:51 AM

as you shouldn't

it's like calculus

you need to learn how the stuff works before you get to use the easy way out, otherwise you don't really know what you're doing

12/8/2008 11:03:16 AM

But you CAN set it up locally on your box (use easyPHP) and learn the syntax for some of those basic sql elements, such as in creating tables, altering tables, and viewing data. It shows you the sql that is used every time you execute an action.

12/8/2008 11:04:15 AM

Quote :

"you need to learn how the stuff works before you get to use the easy way out, otherwise you don't really know what you're doing"

I don't understand this line of thinking.

Where the UNSIGNED attribute has to be defined in relation to NOT NULL doesn't have any bearing on whether or not you understand what you're doing, just whether or not you know the exact syntax that you wind up having to use a handful times per project.

12/8/2008 11:34:28 AM

Entry.level.class

12/8/2008 12:00:19 PM

Quote :

"Where the UNSIGNED attribute has to be defined in relation to NOT NULL doesn't have any bearing on whether or not you understand what you're doing, just whether or not you know the exact syntax that you wind up having to use a handful times per project."

knowing things like that means you have at least a simple understanding of how mysql parses sql statements, and, in turn, how the database engine works

this knowledge, combined with a good working knowledge of proper schema design, helps you IMMENSELY when you start looking at large relational databases

if you rely on a crutch like phpmyadmin, you won't get that. don't get me wrong, it's nice to have around for day-to-day use, but you need to understand what it's doing.

12/8/2008 12:22:58 PM

Quote :

"knowing things like that means you have at least a simple understanding of how mysql parses sql statements, and, in turn, how the database engine works"

No, it doesn't. All it means is that I know that there are two attributes a column could have.

What you're saying would be the equivalent of going to a mechanic and going "you didn't put together the car piece by piece, so you obviously don't understand how it works"

12/8/2008 2:28:10 PM

get sqlyog. fuck that phpadmin garbage

http://www.webyog.com/en/downloads.php#sqlyog

12/8/2008 2:31:23 PM

Quote :

"What you're saying would be the equivalent of going to a mechanic and going "you didn't put together the car piece by piece, so you obviously don't understand how it works""

Actually, it's more like going to an engineer and saying, "you don't know how to take an integral by hand, so you obviously are not qualified to build bridges. Please have a real engineer double-check your work."

Basically, it's the difference between a doctor and a nurse.

12/8/2008 5:31:15 PM

no, actually it's more like Internet pricks bitching about "kids these days" using <insert tool here> to make programming <insert your pet language here> a bit easier because "back when I was in school we had to walk through 6 feet of snow just to put one punch card in the main frame to execute an INSERT statement"

12/8/2008 6:08:29 PM

^

12/8/2008 6:50:57 PM

Quote :

"no, actually it's more like Internet pricks bitching about "kids these days" using to make programming a bit easier because "back when I was in school we had to walk through 6 feet of snow just to put one punch card in the main frame to execute an INSERT statement""

you completely missed my point

12/8/2008 6:59:15 PM

12/8/2008 7:20:55 PM

^^ I get, and sympathize with, your point on a large scale.

However, wut is trying to write a simple blogging engine for a class. He wasn't hired by SUN to optimize their customer relations database.

12/8/2008 7:41:14 PM

12/8/2008 7:59:29 PM

In fact I have some PHP questions if anyone could help me out. The classes I missed were all when she was talking about PHP and MySQL

12/8/2008 8:02:27 PM

What class is this anyways? I can help you with PHP stuff...what are your questions?

12/8/2008 8:25:23 PM

Web development class at UNC

I dont even know how to store posts in to the database yet. Teaching yourself this stuff in a class that meets 2 times per week is pretty uncomfortable

Store a new post into the database

Write a php script to do the following:
Check whether a form is submitted
If submitted, save the data into the database
- Connect to the MySQL database (after you are done interacting with the database, close
the connection)
- Insert data into the post table (in your case, yourlastname_post).
- In your insert statement, use MySQL’s now() function to store the posting time
- Depending on the result (success/failure) of the operation, display appropriate message to
the user
Otherwise, display the input form

After writing and TESTING the script, drop your post table and recreate the table. Add at least 3
‘clean’ posts.


Display posts
Now that we have data in the database, the next step is to retrieve and display them when a
request comes.

Write a php script to do the following:
Connect to the MySQL database (after you are done interacting with the database, close the
connection)
Retrieve the 3 most recent posts from the database
- Use the ‘LIMIT’ clause in your select statement
Display posts
- Loop through the result set and display posts in turn.
- Define and use CSS styles for displaying posts (in a separate CSS file)
- For each post, display all the fields except the id (postid)
- At the bottom of each post, there should be two links: “See details,” “Comment on this.”
For now, we will leave the links as dummy links (e.g. <a href=”#”>Comment on this
</a> )


Requirements
All the functionalities described above should be implemented as specified.
Your code should be readable. Include meaningful comments.
The html code that your script creates should be standard compliant (XHTML strict).
Most importantly, your script should WORK. The working version of each PHP script
should be up and linked from your INLS572 homepage.


[Edited on December 8, 2008 at 8:55 PM. Reason : .]

12/8/2008 8:42:06 PM

so what out of that do you need help with?

this should get you started:
http://www.php.net/variables.external (look for $_POST)
http://www.php.net/manual/en/book.mysql.php (you're interested in mysql_connect, mysql_query, mysql_fetch_array, mysql_num_rows and mysql_close)
http://www.php.net/manual/en/control-structures.if.php
http://www.php.net/manual/en/control-structures.while.php
http://www.php.net/manual/en/function.echo.php

12/8/2008 9:39:13 PM

^ what he said...haha, I was just going to say look at php.net in general.

12/8/2008 10:23:37 PM

^ Ive got some of that already completed. Ive been spending time on php.net since Ive been out. But now its crunch time and I dont have the time to "figure it out". This is what I have so far, and its probably not very good.

Ive created my database tables but I forgot to join them. I have to go back in and do that now.

<html>

	<head>

	<title>A sample blog page</title>

	</head>

		<body>

			<H1> Make a new post</H1>
			<br />
			<br />
		
			<form method="POST" action="blog.php">
			
			<!--Store in database -->
			Title: <input type="text" name="title" value="" size="50" maxlength="250" />
			<br />
			<br />
			
			<!--Store in database -->
			<select name="category">
  				<option>Category 1</option>
 				<option>Category 2</option>
 				<option>Category 3</option>
			</select>
			
			<input type="radio" name="status" value="public" checked="yes"/>Public | 
			<input type="radio" name="status" value="private"/>Private 	
			<br />
			<br />
			
			<!--Store in database -->
			Content: 
			<br />
					
			<textarea cols="80" rows="50" name="content">
			</textarea>
			<br />
			<br /> 

			
			<input type="submit" value="Post" />
			<br />
			<br />
			
			<input type="reset" value="Reset" />

			
			
			</form>
			<br />
			<br />
			

		</body>

</html>

<?php
//variables
$title =$_POST['title'];
$content =$_POST['content'];
$category =$_POST['category'];
$stamp = date("F j, Y, g:i a");
$status =$_POST['status'];



if ($status=="private"){
print "This is private";
}
else {


?>



<html>
	<body>
		<p>
			<?php print $title; ?>
			<br />
			Posted on: <?php print $stamp; ?>
			<br />
			<?php print $category; ?>
		</p>

		<p>
		<?php print $content; ?>
		</p>

</body>

</html>

<?php
}
?>

12/8/2008 10:25:30 PM

Can't believe no one pointed this out yet

There is something real fucking rich about a kid taking a technology class at UNC and is over here on a State message board needing help.

I went to State. I picked up php and mysql in my free time. Why? Because I wanted to do a personal blog waaaaayyy back in 1999...before blog existed in the vernacular. That's just how State kids roll.

We're not your rival.

[Edited on December 8, 2008 at 10:27 PM. Reason : .]

12/8/2008 10:27:08 PM

Kid? Im probably as old if not older than you.

In your spare time huh?

In my spare time I put in 20 hours a week as a wireless analyst managing our infrasturucture, studying for my CCNA Wireless, teaching Tae Kwon Do 3 nights a week, practicing and competing with the UNC TKD team 2 times a week, and be in a Fraternity. Oh lets not forget my other job working the door at a bar on Fri and Sat nights. Wait, theres more: the 4 or 5 blogs I already have and 2 wiki sites Im trying to get off the ground.

I dont care where you get help from or what university it comes from. I already have an account here so I ask here. UNC doesnt have a lot of students that major in CS, and most that are familiar with what Im doing within my school (IS) is by graduate students, few and far between. This is exam week, availability of help is quite limited.

Unless you contend that you never ever had to ask anyone a question about PHP or MySQL, leave the UNC/NC State shit in the sports section. This is a place where everyone faces the same learning obstacles, regardless of university affiliation.


[Edited on December 8, 2008 at 10:53 PM. Reason : .]

12/8/2008 10:31:01 PM

except you have to be adorned in the most horrific color known

12/8/2008 10:38:30 PM

attn:book re:cover

12/8/2008 10:42:01 PM

oh lawd, you need help son... or your teacher just sucks that much

i really don't feel like doing your homework for you right now... you should be able to figure it out. you said you don't have the time - well, you should have thought about that earlier in the semester when you DID have time to learn.

12/8/2008 11:47:07 PM

I missed 6-8 weeks of classes with 2 herniated disks in my back. Class meets 2 times per week.

Teacher teaches pretty well, I just missed the important lectures.

[Edited on December 9, 2008 at 12:02 AM. Reason : .]

12/9/2008 12:02:43 AM

1) you need to stick the functions in the php code.

2) just do the connect at the beginning

3) then you would use mysqli_query to do you inserts/selects

4) mysqli_query should return a result set which you would assign to a variable
like $result = mysqli_query. For inserts you probably don't need to do this except if you want to check if it was successful.

5) to access each row of the result set you use mysqli_fetch_array($result) in a loop...such as:
while ($row = mysqli_fetch_array($result)) {
echo $row['content'];
}
mysql_fetch_array iterates the result set for you automatically

6) just stick the close function at the end of the php code

Hopefully this helps? PS I'm assuming mysqli shit works the same as the basic mysql functions. I think thats the case for the basic functions but I've never messed with mysqli functions in php so I'm not making guarantees

12/9/2008 12:03:21 AM

^ that helps explain a lot for me! Tremendous help!

Thanks!

12/9/2008 12:06:58 AM

bow-chicka-wow-wow

12/9/2008 5:00:22 PM

index.php

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
    <title>A sample blog</title>
    <link href='index.css' rel='stylesheet' type='text/css'>
</head>

<body>

<p class=navigation><a href=add_post.php>Add a new post</a></p>
<div id= "content">

<?php
error_reporting(E_ALL);

    /* 1. Connect to the database - needs to match config.php */
    include 'config.php';
    $link = mysqli_connect($config_mysql_host, $config_mysql_user, $config_mysql_passwd, $config_mysql_dbname)
    or die("Could not connect to database: " . mysqli_connect_error());
        
    /* 2. Get all the course information from the database */
    // construct the sql statement 
    $sql = "select * from jones_posts, jones_categories where jones_posts.postid=jones_categories.postid";
    
    // send the sql statement to MySQL
    $result = mysqli_query($link,$sql) or die(mysqli_error($link)); 

    /* 3. Display the results */
    $count = mysqli_num_rows($result);
    echo "<h1> Post Contents </h1>";
    echo "<p> $count Posts</p>" ;
    if ($count) {
        while ($post = mysqli_fetch_assoc($result)) {
        	if ($post['private']==0){
            echo "<p>";
            echo "{$post['category']}: {$post['subject']} <p />"; 
            echo "{$post['content']} <br />";
            //echo "<a href='view_course.php?id={$post['postid']}'>more information</a>";
            echo "</p>";
            } else {
            echo "This post is private";
            
            }
        }
    } 
?>

</div>

</body>
</html> 
 

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" 
   "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" lang="en" xml:lang="en">
<head>
    <meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1" />
    <title>Make a post</title>
    <link href='index.css' rel='stylesheet' type='text/css'>
</head>
<body>
<p class=navigation><a href=./index.php>Home</a></p>

<?php

    if (isset($_POST['submit'])) { 
        // Connect to the database
     include 'config.php';
   	 $link = mysqli_connect($config_mysql_host, $config_mysql_user, $config_mysql_passwd, $config_mysql_dbname)
   	 or die("Could not connect to database: " . mysqli_connect_error());

        
        
        // Get the input values passed from the form 
        // & convert speical characters  into html entities using htmlspecialchars() [(', ", &, <, > => (", &, <, >)]
        //   to prevent user-supplied text from containing HTML markup
        //**change these variables**//
        $category = htmlspecialchars($_POST['category']);
        $subject = htmlspecialchars($_POST['subject']);
        $content = htmlspecialchars($_POST['content']);
        $status = htmlspecialchars($_POST['status']); 
        
        if($status == 'public') {
        	$status= 0;
        } else {
        	$status=1;
        }
        
        
        // construct the sql statement for inserting data
        //**This is the table the data is stored in**//
        $sql = "insert into jones_posts (subject, content, private)
                values ('$subject', '$content', '$status')";
                
        // send the sql statement to MySQL
        $result = mysqli_query($link,$sql) or die(mysqli_error($link));
        
        $postid= mysqli_insert_id($link);
        
        // construct the sql statement for inserting data
        $sql = "insert into jones_categories (category, postid)
                values ('$category', $postid)";
                
        // send the sql statement to MySQL
        $result = mysqli_query($link,$sql) or die(mysqli_error($link));

                
        // report the result
        echo mysqli_affected_rows($link)." row(s) inserted";
        
        
        // close the connection
        mysqli_close();
    } else {  // 'else' block begins here. If the form is NOT submitted the following code will be executed.
        
        
        
        // print a input form 
?>


<!-- Change the labels and titles for this form -->
  <form action="<?php echo $_SERVER['PHP_SELF']?>" method="post">
  
  <p>
  
	<label for="subject">Title: </label>
    <input type="subject" name="subject" size="30"/><br /><br />
     
     
    <label for="category">Category: </label>
		<select name="category">
  				<option>Category 1</option>
 				<option>Category 2</option>
 				<option>Category 3</option>
		</select> <br /><br />
		
		
	<input type="radio" name="status" value="public" checked="yes"/>Public | 
	<input type="radio" name="status" value="private"/>Private <br /><br />
     
     
    <label for="content">Content: </label><br />
    <textarea name="content" cols="70" rows="20"></textarea>
    
  </p>
  
  <p><input type="submit" name="submit" value="SUBMIT" /></p>
  
  </form>

<?php
}  // 'else' block ends here
?>
</body>
</html>  

12/15/2008 2:38:40 AM

YO

I have a table that looks something like this

+-------+-------+
| COL_1 | COL_2 |
+-------+-------+
| Bob   | Dave  |
| Dave  | Bob   |
| Jim   | Dave  |
| Bob   | Jim   |
+-------+-------

+---------+---------+-------+
| Dudes_1 | Dudes_2 | Count |
+---------+---------+-------+
| Bob     | Dave    |     2 |
| Bob     | Jim     |     1 |
| Dave    | Jim     |     1 |
+---------+---------+-------+

2/27/2009 11:12:14 PM

select if(f1<f2,f1,f2) as col1, if(f1>=f2,f1,f2) as col2, count(*)
from ernie
group by col1, col2

[Edited on February 27, 2009 at 11:50 PM. Reason : performance-wise i dunno]

2/27/2009 11:25:24 PM

Hmm

That's close, but the output is a bit wonky

I'll fiddle with it

[Edited on February 28, 2009 at 12:00 AM. Reason : thx]

2/27/2009 11:48:35 PM

i should probably know this, but i can't for the life of me think of an easy way to do it

say i have a table with ~50 fields...i want to pull ALL of the info, so SELECT * is fine, except for the fact that i want to only pull information where it checks 4 fields to determine if they're unique

for example, let's say the 4 fields i'm concerned with are REGDATE, FNAME, LNAME, and COMPANY...there might be a case where there's a duplicate entry (let's say someone misspells their email address and registers a second time)...i want to check against FNAME, LNAME, and COMPANY and if there's a duplicate of those exact same fields, check the REGDATE and take the most recent submission

does that make sense?

3/11/2009 10:05:30 AM

select *, count(*)
from table
where count(*) > 1
group by fname, lname, company
order by regdate

3/11/2009 10:30:44 AM

While that might work in this case, it's important to note that when ORDER BY and GROUP BY in MySQL function independently of one another. ORDER BY has no affect on how columns are grouped.

3/11/2009 10:54:00 AM

^^ putting that in makes it conk out...the bit below works, but it's displaying the older entries, not the newer ones (because REGDATE isn't even referenced, i know...but i don't know where to reference it that it would compare and choose the newer)

SELECT *, COUNT(*)
FROM table
GROUP BY LNAME, FNAME, COMPANY

^ yeah, i tweaked it to just sort by last/first/company since ORDER BY doesn't matter afaik

3/11/2009 10:59:54 AM

the order by regdate is that part that picks the newest or oldest record

i know what you and stein are saying, and you wouldn't be able to sort by anything else, but i don't see why it wouldn't work.

alternatively, you could do a subquery


SELECT *, COUNT(*), (select min(regdate) from table tt where tt.lname=t.lname and tt.fname=t.fname and tt.company=t.company)
FROM table t
where count(*) > 1
GROUP BY LNAME, FNAME, COMPANY


[Edited on March 11, 2009 at 11:12 AM. Reason : but that's bound to be pretty inefficient too]

3/11/2009 11:03:55 AM

well, all the ORDER BY does is resort the table content so that it's (obviously) arranged by REGDATE...GROUP BY already does that for me using LNAME, etc.

the problem isn't that the ORDER BY REGDATE isn't working, it's that while the query is successfully pulling only one of the duplicate entries (which i want it to do), it's pulling the older one, not the newer...i assume that's because it's finding it in the table first (since it was the original submission) and then kicking out the second one (the newer entry) as a duplicate

[Edited on March 11, 2009 at 11:20 AM. Reason : .]

3/11/2009 11:19:38 AM

so do order by descending

3/11/2009 11:22:04 AM

Quote :

"the problem isn't that the ORDER BY REGDATE isn't working, it's that while the query is successfully pulling only one of the duplicate entries (which i want it to do), it's pulling the older one, not the newer...i assume that's because it's finding it in the table first (since it was the original submission) and then kicking out the second one (the newer entry) as a duplicate"

Correct. ORDER BY doesn't affect GROUP BY unless you ORDER BY on a field by field basis.

Question: does your table have a unique identifier for each row? Like an auto_increment field?

3/11/2009 11:34:00 AM