1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

php to access database

Discussion in 'PHP' started by ataloss, Feb 18, 2020.

  1. #1
    my objective is to select records from database to remember info, click on and go to the target and
    update the date last used, visit count, time visited and if the info hasn't been saved, save it.
    I do this in html, see the records, click on a button and go to target of interest... using Xampp
    server w/databases, and access thru php I can update(crud).

    My Xammp is running, my database is confirmed, my php doesn't seem to work in accessing the records.
    I've tried other forums to no avail.
    Below is my latest feeble attempt. I'm hoping for adviceI have a screenshot of resulting
    display, any help? c'mon, there's room.
    ===========================================================
    here's my code - hey I feel the lauhter
    ---------------------------------------
    
    <!DOCTYPE html><html>
    <head></head>
    <BODY><center>
    
      <?php
    echo "<center>";echo $date->format('d-m-Y H:i:s a');echo "</center>";
    $id="''";
      $con=mysqli_connect("localhost","root","","homedb");
    
    // ============== check connection
    
      if(mysqli_errno($con))
      {echo "Can't Connect to mySQL:".mysqli_connect_error();}
      else
      {echo "</br>";}
    
    // ==========This creates the drop down box using records in the table
    
      echo "<select name= 'target'>";
      echo '<option value="">'.'---select email account ---'.'</option>';
      $query = mysqli_query($con,"SELECT target FROM lookuptbl");
      $query_display = mysqli_query($con,"SELECT * FROM lookuptbl");
      while($row=mysqli_fetch_array($query))
      { echo "<option class=highlight value='". $row['target']."'>".$row['target']
      .'</option>';}
      echo '</select>';
      ?>
    <input type="submit" name="submit" value="Submit"/>
      </form></body></html>
    
      <?php
    error_reporting(E_ALL ^ E_NOTICE);
    // error_reporting(0);
      $con=mysqli_connect("localhost","root","","homedb");
      if(mysqli_errno($con))
      {echo "Can't Connect to mySQL:".mysqli_connect_error();}
      if(isset($_POST['target']))
      {
      $id = $_POST['id'];
      $name = $_POST['target'];
      $fetch="SELECT target, purpose, username, password, emailused, visits, lastdate, lasttime, saved
    FROM lookuptbl WHERE target = '".$name."'";
      $result = mysqli_query($con,$fetch);
      if(!$result)
      {echo "Error:".(mysqli_error($con));}
    
    // =============================== this displays the table
    
      echo '<table border="1">'.'<tr>'.'<td bgcolor="#FFD47F" align="center">'. 'email menu'. '</td>
    '.'</tr>';
      echo '<tr>'.'<td>'.'<table border="1">'.'<tr bgcolor="#ccffff">'.'<td>'.'target'.'</td>'.'
    <td>'.'purpose'.'</td>' '<td>'.'username'.'</td>'.'<td>'.'password'.'</td>'.'
    <td>'.'emailused'.'</td>'.'<td>'.'lastdate'.'</td>'.'<td>'.'lasttime'.'</td>'.'<td>'.'saved'.'</td>'.'</tr>';
      // while($data = mysqli_fetch_row($fetch))
      while($data=mysqli_fetch_row($result))
      {echo ("<tr><td>$data[0]</td><td>$data[1]</td><td>$data[2]</td><td>$data[3]</td><td>$data[4]</td>
    <td>$data[5]</td><td>$data[6]</td><td>$data[7]</td><td>$data[8]</td></tr>");}
      echo '</table>'.'</td>'.'</tr>'.'</table>';
      }
      ?>
      </body></html>
    
    Code (markup):

    SEMrush
     
    Last edited by a moderator: Feb 19, 2020
    ataloss, Feb 18, 2020 IP
    SEMrush
  2. hdewantara

    hdewantara Well-Known Member

    Messages:
    467
    Likes Received:
    40
    Best Answers:
    21
    Trophy Points:
    130
    #2
    Hi, any errors / warnings from system log?
    Also, this var doesn't seem to be used:
    $query_display = mysqli_query($con,"SELECT * FROM lookuptbl");
    PHP:
     
    hdewantara, Feb 18, 2020 IP
  3. deathshadow

    deathshadow Acclaimed Member

    Messages:
    9,178
    Likes Received:
    1,728
    Best Answers:
    239
    Trophy Points:
    515
    #3
    A good start would be to bring your skillset and codebase out of the 1990's. You're not just using tags and attributes like CENTER, BORDER, and BGCOLOR that have NOT been valid HTML since 1998, your PHP/SQL methodology is equally behind the times if not outright gibberish. From the use of string addition when not necessary, to multiple echo doing one echo's job, to slopping variables into query strings defeating the entire reason the old mysql_ functions went the way of the dodo and why one should be using mysqli, or even better PDO.

    Gets worse digging deeper with things like tables for nothing, tables wrapping tables, no THEAD/TBODY, TH, or SCOPE with a TR+TD doing CAPTION's job, (there are tags other than TR and TH that go into a table), bizzaro string additions for christmas only knows what...

    I mean seriously, whiskey tango foxtrot:
    echo '<tr>'.'<td>'.'<table border="1">'.'<tr bgcolor="#ccffff">'.'<td>'.'target'.'</td>'.'
    <td>'.'purpose'.'</td>' '<td>'.'username'.'</td>'.'<td>'.'password'.'</td>'.'
    <td>'.'emailused'.'</td>'.'<td>'.'lastdate'.'</td>'.'<td>'.'lasttime'.'</td>'.'<td>'.'saved'.'</td>'.'</tr>';
    Code (markup):
    Whoever wrote that needs to learn how echo works and what it's for. Then this:

     $fetch="SELECT target, purpose, username, password, emailused, visits, lastdate, lasttime, saved
    FROM lookuptbl WHERE target = '".$name."'";
    Code (markup):
    This is 2020, you DO NOT SLOP VARIABLES INTO YOUR QUERY STRINGS! ESPECIALLY if the origin of the value is $_POST! Insecure, outdated, outmoded nonsense. Prepare/execute!

    .. and really the variables for nothing aren't helping you either.

    Guessing WILDLY here, but if I were to do this first I'd switch to using PDO given your data access is more natural with it. Then cleaning up everything else:

    <?php
    
    // set this FIRST!
    error_reporting(E_ALL ^ E_NOTICE);
    // error_reporting(0);
    
    
    echo '<!DOCTYPE html><html><head><meta charset="utf-8">
    <title>Describe this Page</title>
    </head><body>
    
    	<time>', $date->format('d-m-Y H:i:s a'), '</time>';
    
    
    try {
    	$db = new PDO(
    		'mysql:host=localhost;dbname=homedb', // DSN
    		'root', // username
    		''      // password
    	);
    } catch (PDOException $e) {
    	die('Connection failed:' . $e->getMessage());
    }
    
    echo '
    	<form>
    		<fieldset>
    			<select name="target">
    				<option value="">---select email account ---</option>';
    
    $stmt = $db->query('
    	SELECT target
    	FROM lookuptbl
    ');
    
    // if value is same as content of an OPTION, you don't need to say value=""
    
    while ($target = $stmt->fetchColumn()) echo '
    				<option>', $target, '</option>';
    
    echo '
    			</select>
    			<button>Submit</button>
    		</fieldset>
    	</form>';
    
    	// wasn't sure why you checked target, you don't use it...
    
    if (!empty($_POST['target'])) {
    
    	$stmt = $db->prepare('
    		SELECT target, purpose, username, password, emailused, visits, lastdate, lasttime, saved
    		FROM lookuptbl
    		WHERE target = ?
    	');
    	$stmt->execute([$_POST['target']]);
    
    	if ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    
    		echo '
    	<table class="eMailMenu">
    		<caption>E-Mail Menu</caption>
    		<thead>
    			<tr>
    				<th scope="col">target</th>
    				<th scope="col">purpose</th>
    				<th scope="col">username</th>
    				<th scope="col">password'</th>
    				<th scope="col">emailused</th>
    				<th scope="col">lastdate</th>
    				<th scope="col">lasttime</th>
    				<th scope="col">saved</th>
    			</tr>
    		</thead><tbody>';
    
    		do {
    			echo '
    			<tr>
    				<th scope="row">', $row['target'], '</th>
    				<td>', $row['purpose'], '</td>
    				<td>', $row['username'], '</td>
    				<td>', $row['password'], '</td><!-- REALLY?!? Security, what's that? -->
    				<td>', $row['emailused'], '</td>
    				<td>', $row['lastdate'], '</td>
    				<td>', $row['lasttime'], '</td>
    				<td>', $row['saved'], '</td>
    			</tr>';
    		} while ($row = $stmt->fetch(PDO::FETCH_ASSOC));
    
    		echo '
    		</tbody>
    	</table>';
    
    	} else echo '
    		<div class="error">No matching records found</div>';
    
    } else {
    	// should something be done if target and id are empty?
    }
    
    echo '
    </body></html>';
    Code (markup):
    Everything else you're trying to do belongs in an external stylesheet, not slopped into the HTML willy-nilly. Again, this isn't 1997 and you're not writing HTML 3.2

    Warning, drive-by post so that code might have some typo's, but should give you a general idea of how one goes about things. Also notice that whitespace and consistent formatting are your friend.
     
    Last edited: Feb 21, 2020
    deathshadow, Feb 21, 2020 IP
    JEET likes this.
  4. SpacePhoenix

    SpacePhoenix Well-Known Member

    Messages:
    137
    Likes Received:
    17
    Best Answers:
    2
    Trophy Points:
    120
    #4
    Just to expand on that, never trust any submitted data, no matter what the source. Always try and validate it wherever possible. Always use prepared statements when using any variable in a query. I personally use prepared statements even when the source of the data isn't external to the app/script. By doing to, if the source of the data gets changed to an external source at a later date, I don't risk accidentally creating a security flaw
     
    SpacePhoenix, Feb 21, 2020 IP
    JEET likes this.
  5. JEET

    JEET Notable Member

    Messages:
    2,888
    Likes Received:
    242
    Best Answers:
    9
    Trophy Points:
    215
    #5
    I think your problem is the missing "<form>" html tag.

    Then on second page you are using "$_POST" to collect variables.
    Your script will not work because $_POST php variable is empty.

    Add the "<form>" tag just before this line:

    echo "<form action='target_page.php' method='post'>";
    echo "<select name= 'target'>";

    Do not forget the "method='post'" part.

    Currently this below query is sending an empty "$name" to database, meaning no records are matching, as a result nothing is getting displayed.

    $name = $_POST['target'];
    //there is nothing in $_POST, so there is nothing in "$name" as well...
    $fetch="SELECT target, purpose, username, password, emailused, visits, lastdate, lasttime, saved
    FROM lookuptbl WHERE target = '".$name."'";


    The raw query going to database is this:
    select * from lookups where target=''

    Add the "<form" line in the original form and your script will work.

    Also, do not do this kind of coding on a production server.
    Keep your database functions separate in a PHP file, do not mix them with HTML code and PHP directly.

    Also, collecting variables from $_POST or $_GET etc, and then sending them directly to database, without validation or escaping, is a huge risk.

    Use the PDO statements like DeathShadow explained, or at least escape the collected variables.
     
    JEET, Feb 21, 2020 IP
  6. ataloss

    ataloss Active Member

    Messages:
    77
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    63
    #6
    damn old man u still around? I can't believe my luck gettin chewed out again by u. I sure have missed u. glad ur still w/us. gimme a few to absorb and I'll be back
     
    ataloss, Feb 22, 2020 IP
  7. NetStar

    NetStar Notable Member

    Messages:
    2,389
    Likes Received:
    508
    Best Answers:
    21
    Trophy Points:
    245
    #7
    cringecode
     
    NetStar, Feb 22, 2020 IP
  8. ataloss

    ataloss Active Member

    Messages:
    77
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    63
    #8
     
    ataloss, Mar 19, 2020 IP