Accessing SQL Database from PHP scripts

Collapse
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • CNR
    New Member
    • Jan 2007
    • 2
    #1

    Accessing SQL Database from PHP scripts

    Hi All,

    I am able to connect to a database on SQL server1 ,but none of my SQL queries seem to be working. The same queries work fine on a different SQL server, where i have used the SQL queries directly on the database and not though PHP script,as there is a problem connecting to this server.
    Can i know why my SQL queries are not working on SQL server1, while the same queries work on a different server?Unfortun ately i can access the Server1 database only through PHP scripts, so i cant know what is the error on the server. Please let me know. Below is the script.

    [php]<HTML><head><ti tle>Sql test</title></head>
    <body>
    <?php
    $host = "xxxxxx.com ";
    $usr = "xxxx";
    $pwd = "xxxxxxxxxx xx";
    $cid = @mysql_connect( $host,$usr,$pwd );
    echo"$cid<br>";

    $db = "chin";

    $SQL = 'CREATE TABLE Photos (id int(6) unsigned NOT NULL auto_increment, filename varchar(100) NOT NULL default \",gallery varchar(100) NOT NULL default \",PRIMARY KEY (id)) TYPE=MyISAM';
    $SQL2 = 'INSERT INTO Photos VALUES (1,\'PinkRose\' ,\'images\')';
    $SQL3 = 'SELECT * FROM Photos ';

    $result = @mysql_select_d b($db,$cid);

    $result2=mysql_ query($SQL);

    mysql_query($SQ L2);
    $result2 = @mysql_db_query ($db,$SQL3,$cid );
    $res3 = mysql_affected_ rows();
    mysql_close();

    echo"$result :(<br>";
    echo "<p>$result 2 </p>";
    echo"<br> res3= $res3<br>";

    ?>
    </body>
    </html>[/php]
    Thanks ,
    CNR
    Last edited by ronverdonk; Jan 7 '07, 04:56 PM. Reason: format tags
    Tags: None
    • ronverdonk
      Recognized Expert Specialist
      • Jul 2006
      • 4259
      #2
      You don't get results from your query because you don't fetch any rows from the query result object. In order to get the rows you must issue a mysql_fetch_* command against that object. Also you should test for any error after a mysql_ command, only that way you can see what is going on when an error happens. See the following adapted part of your code:
      [php]// CREATE the table
      $result2=mysql_ query($SQL)
      or die("CREATE failed: ".mysql_error() );
      // INSERT some rows
      result3=mysql_q uery($SQL2)
      or die("INSERT failed: ".mysql_error() );
      // SELECT table columns
      $result4 = mysql_query($QL 3)
      or die("SELECT failed: ".mysql_error() );
      // establish no of table columns in result
      $num_fields=mys ql_num_fields($ result4);
      // read each row in the result
      while ($row=mysql_fet ch_assoc($resul t4)) {
      // print each column of each row
      for ($i=0; $i < $num_fields; $i++)
      echo $row[$i].' ';
      echo '<br />';
      }
      $res3 = mysql_affected_ rows();
      mysql_close();
      echo"<br> res3= $res3<br>";[/php]
      Ronald :cool:

        Comment

        • CNR
          New Member
          • Jan 2007
          • 2
          #3
          Hi,

          Thank you very much for your reply. It helped me know what the exact errors were. I could create the table but have some issues with insert.

          The following query
          INSERT INTO Photos2(id,file name,gallery) VALUES (2,\'PinkRose\' ,\'images\')
          gives error -- Unknown column 'gallery' in 'field list'.

          I am using the following query to create the table,but looks lilke the gallery column is not added to the table.
          $SQL = "CREATE TABLE Photos2 (id int(6) unsigned NOT NULL auto_increment, filename varchar(100) NOT NULL default \",gallery varchar(100) NOT NULL default \",PRIMARY KEY (id)) TYPE=MyISAM";
          Can i get some help with this?

          Thanks

            Comment

            Previous template Next
            Working...