Board index   FAQ   Search  
Register  Login
Board index php forum :: Database mySQL & php coding

Most likely this is simple

Codes here !

Moderators: macek, egami, gesf

Most likely this is simple

Postby Carl689 » Sun Oct 20, 2002 12:44 pm

I think that this is easy to do just I am not to quick on PHP and MYSQL stuff yet, newbie. I need a page on my website where it will list every member thats in my databse in on col of a table and in the other col I need it to display how much money they owe to my orginization, but I need to be able to edit this 2nd col and then submit the page so it updates the database. If it matters my Database consists of the following field names : id , login, password, name, email, level, dat, icq, occupation, interests, likes dislikes, address, newsletter, picurl, epassword, active, ip, lastmemo , lastnews, lastboard, lastviewed, lastmemoread, lastboardread. Now if you havn't guessed it yet but I am using a prebuilt MySql & PHP community engine. I use the likes field for their account balance (just b/c why back when I made the site I didn't know how to use MySql at all but edit current fields). If you could make this for me I would greatly appreciate it.
~Carl
P.S. if I was hard to understand up there I will attempt to make the webpage I need in ASCII art !!!!!! :-) _________________________________________________
The Super Edit Page
__________________________________________________

********************************************
*Users........................*Account Balance......................*
********************************************
* User 1......................*.............................................34*
********************************************
*User 2.......................*.......................................23432*
********************************************
*Ect............................*.......................................34323*
********************************************
----------------------
Submit Changes -
----------------------
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Postby Jay » Sun Oct 20, 2002 1:18 pm

What you need to do is pull all the info out of the database, and it is goes through each row, you want to output it to a table. Except as you're outputting it, you're doing it in a form fashion (with all the fields filled in with the database values). Then, at the bottom you can have the option to submit the form which will update all the values in the database. It's a bit complicated but this is just to give you the idea of what you need to do!
Jay
 

Spare Time

Postby Carl689 » Mon Oct 21, 2002 6:49 am

I kind of guessed that much, just I dont know enough PHP yet to do it, if someone has the spare time to write this script :-) that would be great.
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Postby Jay » Mon Oct 21, 2002 12:14 pm

You'd be hard pushed to find someone who'll write it out for you. We're here to help, not provide work for free! But you might be lucky
Jay
 

Ya I know

Postby Carl689 » Tue Oct 22, 2002 6:42 am

Ya I know, but I can always hope :D
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Continued hope

Postby Carl689 » Wed Oct 23, 2002 1:26 pm

Well dont laugh but I attempted to write a script on my own, its not very eligent (niether is my spelling) but I thouhgt it would work.... it doesn't so here's what I got.
Code: Select all
 
<?PHP
include ('edge.header.php');
mysql_select_db("edge",$db);

  // query the DB

  $sql = "SELECT * FROM people WHERE id=$id";

  $result = mysql_query($sql);   

  $myrow = mysql_fetch_array($result);
  while ($myrow = mysql_fetcharray($result)) {
     echo "<form method='post' action=$PHP_SELF?>" ;
     echo "<input type=hidden name='userid' value=$myrow['id'] > ";
     echo "<table><tr><td width=100>User Name</td><td>Account Balance</td>";
     echo $myrow["id"];
     echo "<input type='Text' name='account' value=$myrow['likes']><br>";

     }
     echo "<input type='Submit' name='submit' value='Enter information'>";
     echo "</form>";
?>

now I know this wont update the information yet, but I hoped this would at least show something on the page, instead it gives me an error.
~Carl
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Postby Jay » Thu Oct 24, 2002 2:09 am

Not bad for a first attempt, a couple of points though

Firstly, don't make a separate form for each record. Just use one form (ie put your form tags outside the loop) and separate each record by a userID or something in front of the variable name.

mysql_fetcharray should be mysql_fetch_array (this is where your error lies)

Skip the $myrow = mysql_fetch_array($result); in front of the loop!
Jay
 

Parse Error

Postby Carl689 » Fri Oct 25, 2002 10:27 am

ALright I have changed it to this yet it does'nt work at all .
Code: Select all
<?PHP
include ('edge.header.php');
mysql_select_db("edge",$db);

  // query the DB

  $sql = "SELECT * FROM people WHERE id=$id";

  $result = mysql_query($sql);   
echo "<form method='post' action=$PHP_SELF?>" ;
 
  while ($myrow = mysql_fetch_array($result)) {
    
     echo "<input type=hidden name='userid' value=$myrow['id'] > ";
     echo "<table><tr><td width=100>User Name</td><td>Account Balance</td>";
     echo $myrow["id"];
     echo "<input type='Text' name='account' value=$myrow['likes']><br>";

     }
     echo "<input type='Submit' name='submit' value='Enter information'>";
     echo "</form>";
?>





.. I get a Parse error: parse error, expecting `T_STRING' or `T_VARIABLE' or `T_NUM_STRING' in editaccount.php on line 14

and of course I have no idea what this means
~Carl
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Postby DoppyNL » Fri Oct 25, 2002 10:37 am

Code: Select all
echo "<input type=hidden name='userid' value=$myrow['id'] > ";

You can't add elements from an array in a string like this, they have to be outside the string, like this:
Code: Select all
echo "<input type=hidden name='userid' value=" . $myrow['id'] . " > ";


Greetz Daan
DoppyNL
 

why!!?!?!?

Postby Carl689 » Tue Oct 29, 2002 11:02 am

why wont this work
Code: Select all
<?PHP
    include ('edge.header.php');
   # mysql_select_db("edge",$db);
   
      // query the DB
   
      $sql = "SELECT * FROM people WHERE id=$id";
   
      $result = mysql_query($sql);   
   
    $result = mysql_db_query($edge_database,$q,$mysql);
    echo "<form method='post' action=$PHP_SELF?>" ;
     
      while ($row = mysql_fetch_array($result)) {
        
    #     echo "<input type=hidden name='userid' value=" . $row['id'] . " > ";
         echo "<input type=hidden name='userid' value=" . $row->id . " > ";
         echo "<table><tr><td width=100>User Name</td><td>Account Balance</td>";
         echo $row["userid"];
         echo "<input type='Text' name='account' value=" . $row['likes'] . "><br>";
   
         }
         echo "<input type='Submit' name='submit' value='Enter information'>";
         echo "</form>";
    ?>
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Re: why!!?!?!?

Postby DutchBen » Tue Oct 29, 2002 11:40 am

Carl689 wrote:why wont this work
Code: Select all
   
     $result = mysql_query($sql);   
   
    $result = mysql_db_query($edge_database,$q,$mysql);

     
 ?>


Make sure your connection information in your header file is correct,
$edge_database = "databaseserver"
$q = "username"
$mysql = "password"

Then choose one of the above, but not both ... mysql_query is the prevered one ... the rest of the code looks fine to me

You'll be a true php coder soon :wink:
DutchBen
New php-forum User
New php-forum User
 
Posts: 36
Joined: Thu Oct 24, 2002 10:28 am
Location: Amsterdam

Thanks

Postby Carl689 » Tue Oct 29, 2002 2:08 pm

Thanks..... that was dumb on my part.
Ok now I got it showing me information!!!!!!!!! So I decided to finish it up and make it update the database and guess what... It didn't work, do this is what i got..

Code: Select all
<?PHP
$db = mysql_connect("localhost", "*****", "******") or die ("Could not connect to localhost");
mysql_select_db("edge",$db) or die ("Could not find edge");

$db_name ="people";
   


if(!$submit)
{
    // query the DB
   $query = "SELECT * FROM $db_name";
   $result = mysql_query($query);   


      echo "<form method='post' action=$PHP_SELF?>" ;
           echo "<table><tr><td width=100><b>User Name</b></td><td><b>Account Balance</b></td></tr>";
     
   while ($row = mysql_fetch_array($result)) {
          echo "<tr><td>" . $row['login'] . "</td>";
            echo "<td><input type='Text' name='account' value=" . $row['likes'] . "></tr>";
   
            }
            echo "<input type='Submit' name='submit' value='Enter information'>";
            echo "</form>";
}
if($submit)
    {
        $query = "UPDATE people SET likes='$likes' WHERE login=$login";
        $result = mysql_query($query);
        echo "Thank you! Information updated.";
    }

    ?>



Also how would i go about making it alphabitize the userid's?
~Carl
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Postby Jay » Tue Oct 29, 2002 4:57 pm

So so so very wrong, here's the things you need to correct

In your form output, set the name of the input as an array (name='account[]'). If you want you can set it to a particular index (as long as it's unique) to match it up to the client or whatever.

Next, when the form is submitted, you'll get an array of values. You will then need to perform a separate update query for each and every record that needs updating, at the moment you're treating them as if it's one!

That should see you sorted!
Jay
 

Hmm

Postby Carl689 » Tue Oct 29, 2002 5:05 pm

I am not to sure I know how to do what you are saying
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Ok I tried

Postby Carl689 » Wed Oct 30, 2002 8:20 am

I read over your post and attempted to do that.. well once again it didn' work. Here it is
Code: Select all
<?PHP
      $db = mysql_connect("localhost", "smmborg", "") or die ("Could not connect to localhost");
      mysql_select_db("edge",$db) or die ("Could not find edge");
     
      $db_name ="people";
         
     
     
      if(!$submit)
      {
          // query the DB
         $query = "SELECT * FROM $db_name";
         $result = mysql_query($query);   
      $id = $myrow["id"];
 
      $login = $myrow["login"];
 
      $likes = $myrow["likes"];
 
     
            echo "<form method='post' action=$PHP_SELF?>" ;
                 echo "<table><tr><td width=100><b>User Name</b></td><td><b>Account Balance</b></td></tr>";
           
         while ($row = mysql_fetch_array($result)) {
              
               #  echo "<input type=hidden name='login' value=" . $row['login'] . " >;                 
              echo "<tr><td>" . echo $login . "</td>";
                  echo "<td><input type='Text' name='account' value=" . echo $likes . "></tr>";
         
                  }
                  echo "<input type='Submit' name='submit' value='Enter information'>";
                  echo "</form>";
      }
      if($submit)
          {
              $query = "UPDATE people SET likes='$likes' WHERE login=$login";
              $result = mysql_query($query);
              echo "Thank you! Information updated.";
          }
     
          ?>


Also how when it is working, how would I make it alphabitize it by their login and how would I make it only show rows that contain a value in a certain column? Ex.. Only show records that in $row['AccountHolder'] it says yes. Thanks,

~Carl
...A day will come when I wont be a newbie...it will come...
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Postby DutchBen » Wed Oct 30, 2002 8:53 am

Say you have 10 people and for 5 of them you change theis balance, how is MySQl to know what to change and where if you only run this query once

Code: Select all
$query = "UPDATE people SET likes='$likes' WHERE login=$login";



To solve this Jay means do
Code: Select all
............='Text' name='account[]' value=" .........


insteead of

Code: Select all
........='Text' name='account' value=" .........


This way the variable $account is an array of all different accounts and theyre (new) value. Now you can use foreach http://www.php.net/manual/en/control-structures.foreach.php to traverse the array and insert the new values one by one

eg.
if($submit){
foreach($account as $value){
... INSERT STUFF .......
}
}

Hope this helps ... you can buy us all a couple of beers once you get all of this up and runnig huh
DutchBen
New php-forum User
New php-forum User
 
Posts: 36
Joined: Thu Oct 24, 2002 10:28 am
Location: Amsterdam

Postby Carl689 » Wed Oct 30, 2002 11:01 am

I appoligize again for my newbie-ness but I can't help it, just think how bad I would be If it ws not for you guys. But I dont quite understand what you where telling me about the foreach thing, I understand your point but I dont know how to do it. Also in my attempt to use variables in my code instead of how I did before I no longer get a list of all members and info I used to get. Oh ya , the beer is on me when this is over.

Code: Select all
<?PHP
         $db = mysql_connect("localhost", "*******", "******") or die ("Could not connect to localhost");
         mysql_select_db("edge",$db) or die ("Could not find edge");
         
         $db_name ="people";
             
         
         
         if(!$submit)
         {
             // query the DB
            $query = "SELECT * FROM $db_name";
            $result = mysql_query($query);   
         $id = $row["id"];
     
         $login = $row["login"];
     
         $likes = $row["likes"];
     
       
               echo "<form method='post' action=$PHP_SELF?>" ;
                    echo "<table><tr><td width=100><b>User Name</b></td><td><b>Account Balance</b></td></tr>";
               
            while ($row = mysql_fetch_array($result)) {
                 
                                echo "<tr><td> $login </td>";
                     echo "<td><input type='Text' name='account[]' value='$likes'></tr>";
             
                     }
                     echo "<input type='Submit' name='submit' value='Enter information'>";
                     echo "</form>";
         }
         if($submit)
             {
              foreach($account as $value){   
                 $query = "UPDATE people SET likes='$likes' WHERE login=$login";
                 $result = mysql_query($query);
                 echo "Thank you! Information updated.";
             }
         }
?>


Carl
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Postby Jay » Wed Oct 30, 2002 12:51 pm

Your code has got sooooo many errors, I've corrected most of them, but left a couple of obvious errors that you'll have to hunt down on your own. If you find them, then you'll understand how the script works and why you couldn't get it to work
Code: Select all
$db = mysql_connect("localhost", "*******", "******") or die ("Could not connect to localhost");
mysql_select_db("edge",$db) or die ("Could not find edge");

$db_name ="people";

if(!$submit) {
   // query the DB
   $query = "SELECT * FROM $db_name";
   $result = mysql_query($query);
   
   $id = $row["id"];
   $login = $row["login"];
   $likes = $row["likes"];
   
   echo "<form method='post' action=".$PHP_SELF.">";
   echo "<table><tr><td width=100><b>User Name</b></td><td><b>Account Balance</b></td></tr>";
   
   while ($row = mysql_fetch_array($result)) {
      echo "<tr><td> $login </td>";
      echo "<td><input type='Text' name='account[$id]' value='$likes'></tr>";
   }
   print "</table>\n";
   echo "<input type='Submit' name='submit' value='Enter information'>";
   echo "</form>";
}
if($submit) {
   foreach($account as $id => $value){
      $query = "UPDATE people SET likes='$value' WHERE login=$id";
      $result = mysql_query($query);
      echo "Thank you! Information updated.";
   }
}
Jay
 

Postby Carl689 » Wed Oct 30, 2002 2:00 pm

Ok I must be totally clueless.. just when I open it up.. it showes this
Image
Its odd to me at least that it creates the right amount of rows for the number of entered data but has no information. Maybe this is the obvious thing I am supposed to notice. Another thing is I dont get is the $value in the foreach function. I found the error in the update part
Code: Select all
login=$id
to
id=$id

:-)

Thanks
~Carl[/code]
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Postby Jay » Wed Oct 30, 2002 2:52 pm

Well, have a look at what information you were expecting to be filled in, and then where they are defined.

The correct amount of rows are generated, because it generates a row every time the code loops. But the variables it's been assigned are outside the loop are they not?
Code: Select all
   $id = $row["id"];
   $login = $row["login"];
   $likes = $row["likes"];

What is $row defined as? Figure that one out, and you'll be one step closer. And until you get the form to display right, there's no point checking the update part of it.
Jay
 

Postby Carl689 » Wed Oct 30, 2002 3:05 pm

IT WORKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
the really sad part is I wondered about and I was going to try that but I guess I got side tracked and never did it. I owe you all a round.. but I have one more thing before I am done.. is there a way to sort the user name in alphabetical order? and this may be pushing it but is there a way to only include people that have $accountholder=yes

would it be something like
Code: Select all
       while ($row = mysql_fetch_array($result)) {
       $id = $row["id"];
       $login = $row["login"];
       $likes = $row["likes"];
       $ah = $row["dislikes"];
if ($ah="yes"){         
 echo "<tr><td> $login </td>";
          echo "<td><input type='Text' name='account[$id]' value='$likes'></tr>";
       }
       print "</table>\n";
       echo "<input type='Submit' name='submit' value='Enter information'>";
       echo "</form>";  }
    }


This is just a quick thought and you may laugh at it. I would try and test it myself buy my FTP stopped working just after I got the thing working. But now after I look at my code I just typed up there I dont think it will take inaccount for each individual member... hmm.. :-(... maybe i am thinking I am going to have to do something similar with the $account[$id] but I am not to sure how to do it. or maybe.. can you do an if statement like ... If ($ah="yes" in $id) { yada yada .... ok I will stop. I will try this stuff if FTP goes back up. You guys already have done more than enough you can completely ignore this if you want.

~Carl
You guys are great
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm

Postby Jay » Wed Oct 30, 2002 3:31 pm

To sort the names, use SORT BY login [desc] at the end of your query. You can manipulate the data in a variety of ways before it reaches PHP if you know SQL. I'll leave you to figure out how to only include rows where there is a "yes" statement (look up the LIKE feature)
Jay
 

Postby Carl689 » Wed Oct 30, 2002 4:20 pm

Its done.. it all works perfectly, doing my sorting and stuff in the query before even getting to php was perfect. I would have never thought of it. Thank you all for your help, couldn't have done it without you, hopefully soon I will be good enough to help other people on this forum.
In case you are wandering this is the final script.
Code: Select all
<?PHP
          $db = mysql_connect("localhost", "********", "********") or die ("Could not connect to localhost");
          mysql_select_db("edge",$db) or die ("Could not find edge");
         
          $db_name ="people";
         
          if(!$submit) {
             // query the DB
             $query = "SELECT * FROM $db_name where dislikes like 'yes' order by login asc";
             $result = mysql_query($query);
             
       
             
             echo "<form method='post' action=".$PHP_SELF.">";
             echo "<table><tr><td width=100><b>User Name</b></td><td><b>Account Balance</b></td></tr>";
             
             while ($row = mysql_fetch_array($result)) {
             $id = $row["id"];
             $login = $row["login"];
             $likes = $row["likes"];
               
                echo "<tr><td> $login </td>";
                echo "<td><input type='Text' name='account[$id]' value='$likes'></tr>";
             
      }
             print "</table>\n";
             echo "<input type='Submit' name='submit' value='Enter information'>";
             echo "</form>";
          }
          if($submit) {
             foreach($account as $id => $value){
                $query = "UPDATE people SET likes='$value' WHERE id=$id";
                $result = mysql_query($query);
                      }
       echo "Thank you! Information updated.";
          }
Carl689
New php-forum User
New php-forum User
 
Posts: 13
Joined: Sun Oct 20, 2002 12:21 pm


Return to mySQL & php coding

Who is online

Users browsing this forum: Google [Bot] and 1 guest

Sponsored by Sitebuilder Web hosting and Traduzioni Italiano Rumeno and antispam for cPanel.