web database using PHP and mysql

New Member Joined: Feb 22, 2006 Posts: 5

Alrite guys - here is my attempt at explaining my problem Smile

I am trying to make a web database using PHP and mysql.

I have made a page called page.inc which sits outside the web tree and contains
a class with lots of functions in it. These functions form a template layout
for the site (header, menu, footer etc) that i want to look the same on each
page and then with each new page all i need to do is extend the class and put in
the extra individual bits for that particular page. Also included in page.inc
is a function called DBconnect() which looks something like:

Code:

public function DBconnect()


  {


        @ $db = new mysqli('blah', 'blah', 'blah', 'blah');





        if (mysqli_connect_errno()) 


        {


           echo 'Error: Could not connect to database.  Please try again later.';


           exit;


        }


      


   }

The thought behind putting the DBconnect function in page.inc rather than in
findPC.php for example is that 1) it contains the login info for the db and if
it is out of the web tree its safer and 2) since i will be wanting to connect to
the db on most pages and the connect info doesn't change it makes sense for it
to be part of the template.

However the problem is that when i try and run a query that i have created
inside a function called content() in findPC.php i get the following error:

Fatal error: Call to a member function query() on a non-object in c:\program
files\apache group\Apache\htdocs\webDB\findPC.php on line 16

The current code for content() looks like this:

Code:

public function content()


        {


           $this -> DBconnect();


         echo"<p>Here is where you come to find PCs src="/images/icons/smile.gif"></p>


         <br>";


           $query = "select * from PC;"; 


           $result = $db->query($query);


         echo "<table border ='1'>";


           while ($row=mysql_fetch_rows($result))


         {


            echo"<TR><TD>";


            $row[0];


            echo"</TD><TD>";


            $row[1];


            echo"</TD><TD>";


            $row[2];


            echo"</TD><TD>";


            $row[3];


            echo"</TD><TD>";


            $row[4];


            echo"</TD><TD>";


            $row[5];


            echo"</TD></TR>";


         }


        echo"</table>";


       }

I know that when i call the DBconnect() function from inside content() function
it does connect to the database successfully but i need to get the information
from $db into the content() function in order for the query to work. How do i
do this?

If you need any clarification of what i have said in order to help just ask Smile

cheers

registered · Posted: Wed Feb 22, 2006 8:06 am

Your connect function actually has to return $db

Then you can do this in your content function

Code:




$db = $this -> DBconnect();

Posted: Wed Feb 22, 2006 8:17 am

Thanks, but if your saying i need to add it in, where abouts?

Posted: Wed Feb 22, 2006 7:04 pm

At the end of public function DBconnect()

Code:




return $db

And then you can use that line above

Posted: Fri Feb 24, 2006 10:18 am

Cheers.

i have another question.

I dunno if what i want to do next is possible.

I want to be able to display different tables dynamically. At the moment I can query one of my tables in the MySQL db, split it into rows and then stick each element of $row into the the appropriate column and repeat this for all the rows in the DB using the following code:

Code:




public function displayResults() 


   { 


      $result = $this -> runQuery(); 


      $num_results = $result->num_rows; 


      $num_columns = $result 


      echo '<p>Number of results/records: '.$num_results.'.</p>'; 


      echo "<DIV><table border ='0' cellpadding='5' bgcolor='white'>\n";   //create table and column headings 


      echo "<tr bgcolor='blue'><td><strong>PCID</strong></td><td><strong>Name</strong></td><td><strong>Model</strong></td><td><strong>Tag Number</strong></td><td><strong>Serial Number</strong></td><td><strong>CPU</strong></td></tr>\n"; 


      $i = 0; 


        while($i < $num_results) 


      { 


         $row = $result->fetch_assoc(); 


         echo"<tr bgcolor='0099FF'><td>".$i++; 


         echo stripslashes($row['PCID']); 


         echo"</td><td>"; 


         echo stripslashes($row['Name']); 


         echo"</td><td>"; 


         echo stripslashes($row['Model']); 


         echo"</td><td>"; 


         echo stripslashes($row['TagNo']); 


         echo"</td><td>"; 


         echo stripslashes($row['SerialNo']); 


         echo"</td><td>"; 


         echo stripslashes($row['CPU']); 


         echo"</td></tr>\n"; 


      } 


     echo"</table></DIV>\n<BR>\n"; 


   }

This lets me dynamically show the contents of the table but what i want to do (if possible) is to look up how many columns any particular table has, what the columns are called and then automatically make the table have the correct number of columns all named correctly too (rather than me setting that myself as i have above).

I can figure out how to get it to display the the right number of columns myself (all it needs is nested loops?) but i don't know how to get the info in the first place.

I could just create an extra table that gives me the column info from each of the tables and then query that to find out the column info but i don't really want to. I'm sure there must be a way of getting the info from the actual table but i don't know it

I hope that made sense

Posted: Fri Feb 24, 2006 2:27 pm

Just out of memory, I believe you are looking for mysql_fields functions. Those should give you the columns

Posted: Mon Feb 27, 2006 9:26 am

Thanks

i have another.. sorry if its too much.

I found out how to do it using the array_keys() function (i assume they are the same thing).

I've managed to get most of it working. I've made it so it displays the coulmn names fine, and displays most of the records correctly however it starts off displaying record 2 first and then puts an empty row at the bottom :S

Here is the code i have:

Code:

public function displayResults()


   {


      $result = $this -> runQuery();         //run the query


      $num_results = $result->num_rows;         //count the number of results from thequery and then display


      echo '<p>Number of results/records: '.$num_results.'.</p>';


      echo "<DIV><table border='0' cellpadding='5' bgcolor='white'>\n"; //start table


      $row = $result->fetch_assoc();      


      $columns = array_keys($row);         //create $columns array containing Tableheadings


       echo("<tr bgcolor='blue'>");


       


      //create column headers


      foreach ($columns as $column)


      {


            echo"<td><strong>$column</strong></td>";


       }


       echo("</tr>");


      


      //display table data


      $i = 0;


        while($i < $num_results)


      {


         $row = $result->fetch_assoc();


         echo"<tr bgcolor='0099FF'>";


         foreach ($columns as $column)


         {


            echo"<td nowrap>";


            echo stripslashes($row[$column]);


            echo"</td>";


         }


         echo"</tr>";


         $i++;


      }


     echo"</table></DIV>\n<BR>\n";


   }

Any ideas why it it starts with row 2? I've tried resetting $row but the
command i had didn't work (it complained about it not being an object or
something).

The query is a simple SELECT * FROM PC; style query. I tried changing it to
SELECT * FROM User; and it made the table the right size with right column
headings etc but still had the problem of starting to display from record 2
onwards. I'm so close! But so stuck! Sad

Posted: Mon Feb 27, 2006 10:14 am

Well your code calls

Code:




$row = $result->fetch_assoc();

in the beginning
and then it does another one at the start of the while loop

Posted: Wed Mar 01, 2006 10:47 am

Thanks.

First i'll mumble a bit about what i've done but there is a question at the bottom.

So far i have created a template. I then create the page by calling the template and supplying it a small amount of details (i.e. a query). It then figures out how many columns there are in the table, names them and then draws the table and fills in all of the data from the table. Each column name can be clicked on to arrange the data by that particular column (both ascending and descending) and each record can be clicked on to load a different page with lots of other info on it.

This has taken me about a week of pure struggling. But since it is a template it takes me about 2 mins to now create a page - i just copy the page, rename it and change the query and its all done

Now the problem i'm having is with a function i kind of erm found

Here it is:

Code:




  public function IsURLCurrentPage($url) 


  { 


    if(strpos($_SERVER['PHP_SELF'], $url )==false) 


    { 


      return false; 


    } 


    else 


    { 


      return true; 


    } 


  }

Basically you pass it an URL and it looks up the URL in the address bar and tells you whether the address you gave it is the current page.

This is all well an good until you use the URL to pass data to the script (i have used it to pass the column name and direction in order to be able to arrange the data by any column). As soon as there is this extra data it then says they don't match and it doesn't display the page quite as it should.

For example the URL might look like /findpc.php?col=Name&dir=asc and i only want it to check it against the findpc.php bit of the address

So all i need to do is tell it to ignore everything after (and including) the ? when comparing the addresses. But how?

Posted: Wed Mar 01, 2006 5:33 pm

You can use the split function to return an array from before the ? and after the ?