Page 1 of 3 123 Last
  • Jump to page:
    #1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    America (But which one?)
    Posts
    43
    Rep Power
    2

    Building a MySQL DB


    I'm completely lost. I'm new to MySQL and PHP, but I can usually pick this sort of thing up by simply reading a few tutorials.

    Iím developing a new website for my employer, and I want it to have an easy to use CMS. I canít get past the database portion of developing a CMS. Iíve looked into designing it by hand as well as using both Joomla and Drupal. There is just something that I am not grasping.

    Iíve developed multiple MS Access databases that utilize tables to populate multiple forms with the information that Iíve inputted. In my head, this should be quite similar. I designate in the HTML where to draw the information in the database from, then I have my database, connect it all with whichever language is necessary (PHP), then have a simple, yet attractive admin front end for my employer to use.

    Does this not exist? Is it really not this simple, or is there really no program that makes it this simple? Everything that Iíve read in regards to building the database doesnít ever hit on how to connect each table/field to a specific part of the website. So how in the world do I build it and specify each table/field and what page/section of my site to update? I am so incredibly lost!!!

    Please somebody help and point me in the right direction! A blank shell example of my site is below, on my testing server:

    intelligenceordeath . com / example

    Thank you all ahead of time; any and all help will be greatly appreciated!!!

    -DZ
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by DoctorZeigler
    So how in the world do I build it and specify each table/field and what page/section of my site to update?
    you do this with php

    your php code submits a SELECT query to the database, receives the result, and uses the data to generate the html

    for more information, ask in the php forum
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    America (But which one?)
    Posts
    43
    Rep Power
    2
    Thank you very much for your reply.

    The problem is, I don't even have the database yet. I just spoke with a friend who is currently also learning the ins and outs of MySQL, and I guess I'm just expecting too much from the technology, or I'm expecting the wrong thing.

    The point I am at is simply creating the database to upload to my host. I have nothing. Just a fully developed website that can currently only be edited by going into each file individually and hand-jamming everything.

    What kind of file is a MySQL database? How do I create such a file? Is it possible to explicitly state what each table and field is and what it correlates to on my website?

    I seriously think I am just not understanding the core concept of MySQL.

    Can't I just create a MySQL database from scratch, before even thinking about the PHP? If so, where are there good (and preferably free) resources to help me learn how?
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    you have experience with msaccess, so i will try to answer your questions in that context

    mysql is like the msaccess jet engine -- it only runs sql statements

    when you create a table in msaccess, the msaccess user interface takes what you type into its forms (in design view) and, under the covers, it creates the sql for the jet engine, i.e. the CREATE TABLE statement

    if you want to proceed to run your web site with a mysql back end, with a user interface that you build yourself -- as opposed to implementing a complete solution like joomla -- then you should install mysql on your desktop computer and build a test database there

    there are numerous tutorials on the web which can guide you

    perhaps start with sqlzoo
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    America (But which one?)
    Posts
    43
    Rep Power
    2
    Thanks again for your reply r937,

    I guess I just really don't understand even the core concept of MySQL. I've downloaded MySQL, built some tables, each with the appropriate columns/fields per each "page" of my site. After doing so, I got sidetracked, and realized I could do the same thing through my host's phpMyAdmin. I created a new database with one table, and this table now has 50 columns (1 columns per heading and 1 column per paragraph per page, with 4 headings and 4 paragraphs per page, except for the home page).

    I still don't get it. I don't even know where to start. I have 50 columns, but have no clue how to connect them to each field on my site. Is that the PHP portion of this? If so, I'll move my questions onto that forum.
  10. #6
  11. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,262
    Rep Power
    4279
    Originally Posted by DoctorZeigler
    Is that the PHP portion of this? If so, I'll move my questions onto that forum.
    yes it is, and i've moved this thread for you
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  12. #7
  13. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Originally Posted by DoctorZeigler
    I still don't get it. I don't even know where to start. I have 50 columns, but have no clue how to connect them to each field on my site. Is that the PHP portion of this? If so, I'll move my questions onto that forum.
    Erm, well... not meaning to sound blunt, but if you have a table with 50 columns, you've done something wrong. Well... maybe not "wrong" so much as incredibly inefficient.

    Sure you don't mean 50 rows?
    That'd structurally make more sense.

    Even something as simple as:

    Code:
    CREATE TABLE IF NOT EXISTS `content` (
      `page_id` int(10) unsigned NOT NULL AUTO_INCREMENT,
      `title` varchar(255) NOT NULL,
      `body` text NOT NULL,
      PRIMARY KEY (`page_id`)
    ) ENGINE=MyISAM DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;
    Then you identify the page by page_id, a title is provided just in case you want it, and the body/content of the page.

    That's more or less the bare minimum to facilitate a CMS site, then it grows from there, the more functionality you want to add.

    That's also not breaking things by paragraphs. Not sure why you'd do that when you can just provide the paragraph break in the body you store.

    Edit: Changed the table to MyISAM since you'd need a pretty new version of MySQL to have fulltext searches on InnoDB text fields--which you'll probably want from a CMS.
    Last edited by dmittner; July 17th, 2013 at 05:00 PM.
  14. #8
  15. No Profile Picture
    Grumpier old Moderator
    Devshed Supreme Being (6500+ posts)

    Join Date
    Jun 2003
    Posts
    14,449
    Rep Power
    4539
    MySQL is a database server, similar to ms sql server, oracle, etc., and uses many files on the server computer to operate. A database is not just a single file like MS Access, you need to interact wit the mysql server and tell it what kind of database you want it to create for you.

    You don't have a file to upload unless it's a SQL file with the necessary sql that the database server can execute to create and/or populate your database.

    MySQL is freely available for download and installing on linux, windows and other platforms. As Rudy mentioned, you should download and install on your local computer so you can begin to get familiar with mysql. If you work with Microsoft SQL Server the concepts are very similar to MySQL.

    hth
    ======
    Doug G
    ======
    Bartender to Rene Descartes "have another beer?" Descartes: "I think not" and he vanished.
    --Alfred Bester
  16. #9
  17. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    America (But which one?)
    Posts
    43
    Rep Power
    2
    Apparently I'm worse off than I thought.

    Is it possible that someone take a look at my site and give me a very simple example of what I need to do? I see your example dmittner, but I don't even know where to put that code.

    If possible, I would greatly appreciate it if someone could tell me where to put such code, and an example of how to make it communicate with my site. Maybe the first paragraph on "Page 1" of my site? The modal dialog box can be ignored.

    It seems like no matter how much research I do, I just can't find something that breaks it down into laymen's terms. It would seem that I need someone to literally tell me to "put X code in X file, saved as such, in this directory, then put this code in this file, etc". I'm not looking for someone to design the entire thing for me (I'd be quite bored at work), but I really need to get put on the right track.

    My site is intelligenceordeath . com /example

    Ignore the horrible background. I just needed to slap something on there that looked better than white lol.

    Thank you all for your help so far, and thank you for your patience! I look forward to your replies!

    -DZ

    EDIT: I'd like to start off with this by ignoring the 50 column table I already made. Basically, start from scratch.
  18. #10
  19. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Originally Posted by DoctorZeigler
    Is it possible that someone take a look at my site and give me a very simple example of what I need to do? I see your example dmittner, but I don't even know where to put that code.
    What I provided isn't "code", per se. Entered into MySQL, that'll create a new table for you to use to, well... start from scratch.

    I don't know MS Access (been a decade since I came to within 10 yards of it) but I'm betting it's knowledge you should ignore for this. It might be giving you incorrect preconceptions of how the MySQL/PHP stack operates.

    Maybe the easiest way to think of MySQL, and other databases, is to think of Excel.

    The database is your workbook.
    The sheet is your table.
    You'll have a table for each data context you're dealing with.
    The table has columns representing each property of that context.
    The table has rows, each of which will be an instance of the context.

    So starting with the basics, lets say your context is an entire webpage content. What elements are fundamental to a webpage? For simplicity sake lets say the page Title and the page Body.

    The Title is what shows at the top of the browser window.
    The Body is all the HTML that makes up the structure and content of the page.

    You'll also need an easy way to programmatically identify pages, so we'll use a unique numerical ID for each one.

    Using my table described before, lets go with this:
    Code:
    page_id | title                          | body
    --------|--------------------------------|--------------------------------
    1       | Something Interesting Happened | <html><body>...</body></html>
    2       | Something Else Happened!       | <html><body>...</body></html>
    Our table now has the content for two pages, identified numerically as "1" and "2" respectfully.

    And this is where PHP comes in.

    PHP will be responsible for knowing how to read data out of that table and, eventually for adding it and changing it.

    Make sense so far?
    LinkedIn: Dave Mittner
  20. #11
  21. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    America (But which one?)
    Posts
    43
    Rep Power
    2
    Originally Posted by dmittner
    Make sense so far?
    So far so good. I have no idea where to put (or enter) the original piece of "code" that you supplied me (if not code, what should I call it?). But as far as how the actual database works, I am definitely tracking a bit better.

    After the MySQL is implemented and the database is created, is there a subsequent file that is created that I can view to see what my database looks like? I guess I really don't have to worry about that until after I learn how to implement it and make it all communicate; just curious!

    Thank you for your help so far. You are slowly easing the pounding headache I've had all week because of this!

    -DZ
  22. #12
  23. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Originally Posted by DoctorZeigler
    So far so good. I have no idea where to put (or enter) the original piece of "code" that you supplied me (if not code, what should I call it?).
    "Query" would be the better term. It's in the language used when interacting with MySQL. I'll explain more on that soon.

    After the MySQL is implemented and the database is created, is there a subsequent file that is created that I can view to see what my database looks like?
    While MySQL does have files underneath, they're nothing you have to worry about and they're not files for human eyes, anyway. And I think this is where your experience with MS Access is maybe confusing things for you.

    From the user's perspective, MySQL isn't an application that loads a document for reading and saves a document after you've made changes. It's a persistent service that you ask (ie, "query") for data, or ask to make changes to the data.

    Going in baby steps again, let's go back to our Excel example. You have your sheet, your columns, your rows, and some data in the cells. But it's not you in front of it--it's someone else. For you to find out what the data is, you need to ask that person, then they tell you. If you want the data changed, you ask them to do it and they tell you if they did it, or if there was a problem doing it.

    That's how PHP will interact with MySQL. You'll tell PHP, through programming, what data to ask MySQL for, and MySQL will respond back with it. You then tell PHP what to do with that data.

    So lets look at and begin dissecting some PHP code:
    PHP Code:
    <?php
    $host 
    "localhost"// Hostname of server hosting database
    $dbname "cms";     // Name of database holding our table
    $user "foo";       // Whatever MySQL account username
    $pass "bar";       // Whatever MySQL account password

    try {
        
    $DBH = new PDO("mysql:host=$host;dbname=$dbname"$user$pass);
        
    $DBH->setAttributePDO::ATTR_ERRMODEPDO::ERRMODE_EXCEPTION );  
    } catch(
    PDOException $e) {  
        echo 
    $e->getMessage();  
    }
    Note: I haven't actually used PDO, myself, but it's the recommended way of doing things, so I'lll try to use it in my examples

    Above is how PHP makes its connection to MySQL. Basically it's you and that other person shaking hands, and you introducing yourself. Assuming you're authorized to access the data, then the other person will be ready for you to give them instructions.

    The code above also watches for any problems during the handshake. If the username/password isn't accepted, the MySQL server can't be found, etc., it'll output the error. So if you go to shake the person's hand, trip, and get their finger in your eye, you'll know.

    Assuming that debacle didn't occur and a good connection is made, lets request some data:
    PHP Code:
    // Assume all above code included here

    try {
        
    // Write the MySQL syntax to get the title and body of page #1
        
    $sql 'SELECT title, body FROM content WHERE page_id=1';

        
    // Prepare the syntax for execution
        
    $sth $dbh->prepare($sql);

        
    // Execute the data request
        
    $sth->execute();

        
    // Grab the response and assign the values to variables
        
    list($title,$body) = $sth->fetch();
    } catch(
    PDOException $e) {  
        echo 
    $e->getMessage();  

    This is a pretty light example of a request. It's a static query, meaning we defined it in its entirety to pull specific columns' data from a specific record. Normally this will be more dynamic (such as the page_id being based on a passed parameter), but we'll get to that later if needed.

    This also works because we KNOW page_id #1 is in the database and we KNOW the request should return just that one record. The code would change under normal circumstances, where the record may have been deleted or if there could be multiple records. So again, just a basic example.

    Assuming all the code above works as expected, PHP now has the $title and $body variables, with their values coming from the MySQL table.

    So we've established the connection between PHP and MySQL, sent MySQL a request for data, and brought the data into PHP for further use. That demonstrates the fundamentals of the PHP/MySQL relationship and how they interact. If we can get your environment up to this point, you should have enough to go on to play with it a little yourself and begin making sense of php.net's PDO documentation.

    The absolute most important thing to learn is how to learn. I've tried to clarify the relationship and push you out of the nest, but figuring out how to fly is something you'll need explore, yourself.

    We'll help you get yourself established and the above working for you if you have problems there, and we'll be here to nudge you in the right direction and answer specific questions, but this is about as far as we'll go providing a detailed step by step intro.

    Hopefully it's still making sense and getting you on the right path.
    LinkedIn: Dave Mittner
  24. #13
  25. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    America (But which one?)
    Posts
    43
    Rep Power
    2
    This is definitely starting to make a lot of sense and I appreciate it so much!!!

    I still don't know where to input the query or the php. I'm assuming I create a new php document, but what about the query? I've read (and got lost reading) various articles regarding phpMyAdmin, and the MySQL Databases provided by my host (bluehost); does it go somewhere within one of those services?

    The only other thing I am lost on is this:

    I understand the query for the most part, but am curious about the "page_id". I am fairly certain this is an ID that I designate (correct?), but do I then essentially wrap the content I am performing the query on, with that ID?

    eg. If my page_id were to be "homeID" would it look like this on the home section of my html?

    Code:
    <div id="homeID">
    <rightcolumn> 
    <h1>Header 1</h1> 
    </rightcolumn>
    </div>
    Otherwise, I don't understand how the query knows what to communicate with in the HTML. I understand the the concept of the query itself, and the PHP, but I feel like I am missing one piece of key information.

    Other than that, once I know the answer to that question, I truly feel like I am on the verge of getting this figured out!!

    I am genuinely excited and so incredibly thankful!

    -DZ
  26. #14
  27. No Profile Picture
    Dazed&Confused
    Devshed Novice (500 - 999 posts)

    Join Date
    Jun 2002
    Location
    Tempe, AZ
    Posts
    506
    Rep Power
    128
    Originally Posted by DoctorZeigler
    I still don't know where to input the query or the php. I'm assuming I create a new php document, but what about the query? I've read (and got lost reading) various articles regarding phpMyAdmin, and the MySQL Databases provided by my host (bluehost); does it go somewhere within one of those services?
    For the PHP, for now you can just create "index.php" in your webroot and put the code in there.

    MySQL can be communicated with many ways. PHP can do it, you can do it from a mysql command-line utility, or you have phpMyAdmin doing it as well. You should be able to plug the "CREATE" query into phpMyAdmin by selecting your database and clicking the "SQL" tab at the top. That should give you a big textarea field where you can just copy&paste it in, then hit "Go".

    I understand the query for the most part, but am curious about the "page_id". I am fairly certain this is an ID that I designate (correct?), but do I then essentially wrap the content I am performing the query on, with that ID?
    I threw the ID in there because:
    a) I find it generally good practice to have a unique numerical identifier for table rows, and
    b) It provides a first step into making a basic CMS

    With the examples above there's not much you need to do with it. The code is explicitly using page_id=1 and we haven't yet added any support for the page_id to be anything else.

    Progressing things further, you could have a URL like:
    http://domain.com/index.php?pageId=2

    PHP would take in that pageId parameter, make sure it's "safe" (a whole other important topic) and place it into the query. So you'd instead of something like:
    PHP Code:
        $sql 'SELECT title, body FROM content WHERE page_id=:pageId';
        
    $sth $dbh->prepare($sql);
        
    $sth->execute(array(':pageId'=>$pageId); 
    This is a more dynamic use of the PDO library, showing how you can insert a variable ($pageId) safely into the query syntax by way of a placeholder (ageId).

    Otherwise, I don't understand how the query knows what to communicate with in the HTML. I understand the the concept of the query itself, and the PHP, but I feel like I am missing one piece of key information.
    The relationship between PHP and HTML can sometimes be a confusing one, too. It's my opinion that it's best to think of HTML (or any output format) as being at the end of process flow.

    1. Enter URL/click something in browser
    2. PHP process begins
    3. PHP asks MySQL for data
    4. PHP handles MySQL response
    5. Repeat 3-4 and/or other business logic
    6. PHP outputs HTML

    Many people intermingle 3-6 but I prefer to prepare PHP variables with everything you'll need for the output and then, once done with that, focus specifically on outputting the HTML with any relevant dynamic data embedded within it.

    So including the tail end of that last example:

    PHP Code:
        list($title,$body) = $sth->fetch();
    } catch(
    PDOException $e) {  
        echo 
    $e->getMessage();  
    }
    print 
    "<html>\n";
    print 
    "  <head>\n";
    print 
    "    <title>".htmlspecialchars($title)."</title>\n";
    print 
    "  </head>\n";
    print 
    "  <body>\n";
    print 
    htmlspecialchars($body);
    print 
    "  </body>\n";
    print 
    "</html>\n"
    The preferred way to output stuff varies and I actually prefer using the Smarty template engine to split off the presentation component, but if I don't have Smarty I'll usually fall back to using explicit print statements. It's bulky but I oddly find it among the easiest to look at.

    Anyway, that's us printing out HTML to the client browser, embedding our $title and $body parameters.

    As page bodies are built out, they can include links to other pages by their respective IDs. And thus you begin to have, in practice, a website you can navigate around in which all the content is stored in a database. And being in a database, you can create tools to add/edit what's in it. Thus, a CMS.

    Just a pretty crude one.
    LinkedIn: Dave Mittner
  28. #15
  29. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Jul 2013
    Location
    America (But which one?)
    Posts
    43
    Rep Power
    2
    I feel as though I may be out of luck here. I would honestly understand if you just told me to bug off.

    I thought I was getting it, I've given what you gave me a try, and after, well, nothing happening other than to get a php page that (I knew I should have copied exactly what it said) said some garble with no useful information (nor any apparent errors), I realized a major problem. As far as I can tell, none of this tells me how to implement/build a database into my already existing website.

    My site is technically one very large page (using javascript to fade the "pages" in and out), and while I do believe I understand the core concept of MySQL databases, I don't want to create an empty table from scratch, I want to create a table that will pull the information I already have and populate the database with all the information I already. On my example site, it is simply the words "Header 1" and "Paragraph".

    Essentially I have 33 headers, each with a paragraph below them. I guess what I really need to know is, in the new empty database that I created in phpMyAdmin, what is the SQL and PHP needed in order for the database to build the table based on the existing fields in my html document?

    I guess the order of things would be to create the PHP that is going to communicate with the database and HTML, then run the MySQL query that, communicating with the PHP, would know what to pull from the existing site. All I'd want for "how-to" on this would be my first page.

    Then from there, I can focus on the PHP for the front end (the PHP that will pull the data from the database to present the page), and after that, I can design the CMS.

    If this isn't possible, then I'll ask another question!

    Also, I've moved my example site to my home page, seeing as I haven't had time to develop my own website! intelligenceordeath . com

    EDIT: I'm about to go on small get away until Monday. While I will be looking at this a little more this evening, don't be surprised (and please don't feel like I've blown you off) if you don't hear from me until sometime Monday afternoon. Thank you again for all of your help so far!
Page 1 of 3 123 Last
  • Jump to page:

IMN logo majestic logo threadwatch logo seochat tools logo