MySQL/PHP:  

Tools:

MySQL:

SQL Statements:

1. create Table :

CREATE TABLE `pagecount` (`page` VARCHAR( 6 ) NOT NULL,`count` INT DEFAULT '0' NOT NULL , PRIMARY KEY ( `page` ) ) COMMENT = 'Used to Count Page Visits';

2. Alter Table Structure:

ALTER TABLE `pagecount` DROP `count`

ALTER TABLE `pagecount` CHANGE `page` `page` VARCHAR( 6 ) NOT NULL

ALTER TABLE `pagecount` ADD `count` INT( 4 ) NOT NULL ;

3. DROP TABLE `pagecount`

4. INSERT INTO `pagecount` ( `page` , `count` ) VALUES ('test', '0');

5. SELECT count FROM pagecount WHERE page = 'test' ;

  • SELECT 1 + 1; -> 2

6. UPDATE persondata SET age=age+1; // person data is table, age is column.

PHP:  Hypertext Preprocessor

Example 1:

<html> 

<body> 

<?php

    $myvar = "Hello World"; 

    echo $myvar; 

?> 

</body> 

</html>

This page displays Hello World by browser.

Example 2: Display records from Table:

<html>
<body>
<?php
$db = mysql_connect("localhost", "userid", "password");
mysql_select_db("jdjua_",$db);
$result = mysql_query("SELECT * FROM users",$db);
if ($myrow = mysql_fetch_array($result)) {
printf("User Id Visits<br>\n");
do {
     printf("<a href=\"%s?id=%s\"> %s %s</a><br>\n", $PHP_SELF,$myrow["id"],    $myrow["emailid"], $myrow["visits"]);
} while ($myrow = mysql_fetch_array($result));
} else {
echo "Sorry, no records were found!"; 
}
?>
</body>
</html>

Example 3: Simple Login Form

<html>
<body>
<?php
if ($submit) {
// process form
$db = mysql_connect("localhost", "jdjua_jdjua","jdjua");
mysql_select_db("jdjua_",$db);
$sql = "INSERT INTO users(emailid,password) VALUES ('$emailid','$password')";
$result = mysql_query($sql);
echo "Thank you! Information entered.\n";
} else{
// display form
?>
<form method="post" action="<?php echo $PHP_SELF?>">
E-Mail Id: <input type="Text" name="emailid"><br>
Password: <input type="Password" name="password"><br>
<input type="Submit" name="submit" value="Sumit">
</form>
<?php
} // end if
?>
</body>
</html>

Example 4: Page count for a particular Page:

<html>
<body>
<?php
  $db = mysql_connect("localhost", "jdjua", "xxxxxx");
  mysql_select_db("jdjua_",$db);
  $result = mysql_query("SELECT * FROM pagecount WHERE page =    'test'",$db);
  $myrow = mysql_fetch_array($result);
  printf("Visits: ");
  $count = $myrow["count"] + 1;
  echo $count;
  mysql_query("update pagecount set count = count + 1 WHERE page = 'test'",$db);
?>
</body>
</html>

Statements and syntax: 

  1. Comments: // to make a single-line comment or /* and */ to start and end a large comment block.

  2. <?php :   This indicates the start of a block of PHP code.

  3. ?> :         This indicates the end of the block of PHP code.

  4. Jump in and out of PHP mode even in the middle of a PHP block:

    1. HTML and PHP inter mix

         <?php
      if (strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== FALSE) {
      ?>
      <h3>strpos() must have returned non-false</h3>
      <p>You are using Internet Explorer</p>
      <?php
      } else {
      ?>
      <h3>strpos() must have returned false</h3>
      <p>You are not using Internet Explorer</p>
      <?php
      }
      ?>

        Output:

      <h3>strpos() must have returned non-false</h3>
      <p>You are using Internet Explorer</p>
  5. Configure PHP to use short tags, <?, and ?>, but these are not XML compliant

  6. Semicolon on the end of each line.

  7. Variable: all variables start with the dollar sign symbol.

    1. $_SERVER['HTTP_USER_AGENT'].: Browser of a User. Reserved PHP variable. contains all web server information.?php echo 

      1. <$_SERVER['HTTP_USER_AGENT']; ?> // Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1) will be the output.

      2. An Array element

  8. if ($myrow = mysql_fetch_array($result)) { ... };

  9. while ($myrow = mysql_fetch_row($result)) { printf("<tr><td>%s %s</td><td>%s</td></tr>\n", $myrow[1], $myrow[2], $myrow[3]); }

  10. do { printf("<tr><td>%s %s</td><td>%s</tr>\n", $myrow["first"], $myrow["last"], $myrow["address"]); } while ($myrow = mysql_fetch_array($result));

  11. Functions: 700 functions

    1. php phpinfo(): Information about your server, internal Web server environment variables, the options that are compiled, and on and on.

    2. mysql_connect("localhost", "user", "password");: opens a link to a MySQL server on the specified host (in this case it's localhost) along with a username and Password.

    3. mysql_select_db("jdjua_",$db); : Select database using information in $db.

    4. mysql_query("SELECT * FROM users",$db); : run query and returns result.

    5. printf("Email Id: %s<br>\n", mysql_result($result,0,"emailid")); : display particular row a particular column.

    6. strpos() is a function:  searches a string for another string.

      <?php
      if (strpos($_SERVER['HTTP_USER_AGENT'], 'MSIE') !== FALSE) {
         echo
      'You are using Internet Explorer.<br />';
      }
      ?>

Click to go to  Home Page