Jump to content

Search the Community

Showing results for tags 'pdo'.

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • Welcome to PHP Freaks
    • Announcements
    • Introductions
  • PHP Coding
    • PHP Coding Help
    • Regex Help
    • Third Party Scripts
    • FAQ/Code Snippet Repository
  • SQL / Database
    • MySQL Help
    • PostgreSQL
    • Microsoft SQL - MSSQL
    • Other RDBMS and SQL dialects
  • Client Side
    • HTML Help
    • CSS Help
    • Javascript Help
    • Other
  • Applications and Frameworks
    • Applications
    • Frameworks
    • Other Libraries
  • Web Server Administration
    • PHP Installation and Configuration
    • Linux
    • Apache HTTP Server
    • Microsoft IIS
    • Other Web Server Software
  • Other
    • Application Design
    • Other Programming Languages
    • Editor Help (PhpStorm, VS Code, etc)
    • Website Critique
    • Beta Test Your Stuff!
  • Freelance, Contracts, Employment, etc.
    • Services Offered
    • Job Offerings
  • General Discussion
    • PHPFreaks.com Website Feedback
    • Miscellaneous

Find results in...

Find results that contain...


Date Created

  • Start

    End


Last Updated

  • Start

    End


Filter by number of...

Joined

  • Start

    End


Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Age


Donation Link

  1. Hello PHP freaks I'm a newbie so bear with me. I have a generic problem and I'm sure code already exists to solve it. I need to have a dynamic sql string variable that will only produce an insert statement with database column names corresponding to input fields on the form that contain values, ignoring the blank ones. I know some looping function like foreach might be used, but if I use it on the $_POST array I'll get the values, not the keys. (the first field, well_no, is a primary key and is held in a session variable, so I know I'll be inserting a value for that column) $ins = "well_no,"; foreach($_POST as $field) if (!empty($field)){ $ins .=$_POST[$field].","; } And I get a warning "Warning: Undefined array key "production in... Here "production" is actually a form input. the problem is my insert statement is hard-coded: $sql = "INSERT INTO well_parent (well_no, easting, northing, purpose, country, admin1, admin2, admin3, settlement, orig_wellno, date_completed, coord_sys, elev, status) VALUES (:well_no, :easting, :northing, ... How can I adjust the sql statement and also the $data array holding the variables. Here's the context: $sql = "INSERT INTO well_parent (well_no, easting, northing, purpose, country, admin1, admin2, admin3, settlement, orig_wellno, date_completed, coord_sys, elev, status) VALUES (:well_no, :easting, :northing, :purpose, :country, :admin1, :admin2, :admin3, :settlement, :orig_wellno, :date_completed, :coord_sys, :elev, :status)"; $stmnt = $pdo->prepare($sql); $data = [':well_no'=>$well_no, ':easting'=>$easting, ':northing'=>$northing, ':purpose'=>$purpose, ':country'=>$country, ':admin1'=>$admin1, ':admin2'=> $admin2, ':admin3'=>$admin3, ':settlement'=>$settlement, ':orig_wellno'=>$orig_wellno, ':date_completed'=> $date_completed, ':coord_sys'=> $coord_sys, ':elev'=>$elev, ':status'=>$status]; $stmnt->execute($data);
  2. Hi guys! I've tried to insert data inside an input's value but the input goes like it is hidden, When I inspect the page it shows that there is no input inside my form. I've tried to move the code to the top of page but nothing is changed always a hidden input while it is not hidden at all as you can see below: <div class="mb-3"> <?php //Checking if submit button is clicked if (isset($_POST['submit'])) { //database cn $db = new PDO("mysql:host=localhost;dbname=centrify","root",""); $username = $_POST['user']; $stmt = $db->prepare("SELECT * FROM agencies_data WHERE agency_user = ".$username.""); $stmt->execute(); ?> <input class="form-control" type="text" name="oid" value="<?php while($item = $stmt->fetch()) { echo $item['agency_user']; } ?>"> <?php } ?> </div> I've tested a lot of placements but it doesnt work for me.
  3. Here is my code, I am trying to create a function that connects to my local SQL DB using PDO instead of mysqli It looks like I can connect, the issue is that...I can't get my error message to show. "Could not connect to the database" will not come up, when I know that my user name and password are incorrect. I try changing the root name to test to get the error but it doesn't show up ....I can't see my mistake <?php //load test ...un comment exit test db con //exit ('test db con'); } class DB { protected static $con; private function _construct() { try{ self::$con = new PDO( 'mysql: host= localhost; dbname=testdb', 'root', 'password'); self::$con->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); self::$con->setAttribute( PDO::ATTR_PERSISTENT, false ); } catch (PDOException $e) { echo "Could not connect to database."; exit; } } public static function getConnection(){ //If this instance was not beem started, start it. if(!self::$con){ new DB(); } //Returns Writeable db connection return self::$con; } } ?>
  4. Howdy folks, I have decided, after a discussion with Barand, to finally hang up the MySQLi shoes and move over to the dark side of PDO. I am trying to Update a profile, for example, but it is not working. No errors or anything. New to PDO so would love some help on figuring out where I am going wrong. Probably everywhere knowing me lol. Here is the dreaded code: if(isset($_POST['submit'])){ $id = trim($_SESSION['id']); //$trn_date = trim($db, date("Y-m-d H:i:s")); //$password = $db->real_escape_string(md5($_POST['password'])); $image = trim($_FILES['image']['name']); $name = trim($_POST['name']); $phone = trim($_POST['phone']); $email = trim($_POST['email']); $address = trim($_POST['address']); $license_number = trim($_POST['license_number']); $position = trim($_POST['position']); $role = trim($_POST['role']); $submittedby = trim($_SESSION["username"]); // image file directory $target = "images/".basename($image); if(!empty($_FILES['image']['name'])) { $sql = "UPDATE users SET name = :name, email = :email, phone = :phone, address = :address, license_number = :license_number, position = :position, role = :role, submittedby = :submittedby, image = :image"; }else{ $sql = "UPDATE users SET name = :name, email = :email, phone = :phone, address = :address, license_number = :license_number, position = :position, role = :role, submittedby = :submittedby"; } $stmt= $db->prepare($sql); if (move_uploaded_file($_FILES['image']['tmp_name'], $target)) { $msg = "Image uploaded successfully"; }else{ $msg = "Failed to upload image"; } if(!$stmt){ if ($stmt->execute()){ $message = ' <i class="fa fa-check text-danger"> Something went wrong please contact the server admin.</i>'; } else{ $message = ' <i class="fa fa-check text-success"> Record Updated!</i>'; } } } Any help folks would be appreciated
  5. Could someone explain to me why this connection/query succeeds <?php DB::getInstance()->query("SELECT username FROM users WHERE username=?", array("TechnoDiver")); But when I do this it comes back as failed <?php $user = DB::getInstance()->query("SELECT username FROM users WHERE username=?", array("TechnoDiver")); if($user) { echo "success -> "; } else { echo "fail -> "; } like I said I ran tests for the DB->query in the query method and it comes back successfully. It's only when I try to assign it that it comes back failed. Why??
  6. I wrote this really nice posting system for a site I'm working on. Problem is, I messed it up somehow, and now I can retrieve $_POST variables so I can post stuff to a MySQL database. I'm really new to PHP, and I have no idea what I did wrong. HTML code: <!DOCTYPE html> <html> <head> <meta charset="utf-8"> <meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no"> <meta name="description" content="The PPC Planet software archive."> <meta name="author" content="JohnS and VP44"> <title>PPC Planet Public Archive</title> <link rel="canonical" href="https://getbootstrap.comhttps://getbootstrap.com/docs/4.5/examples/jumbotron/"> <!-- Bootstrap core CSS --> <link href="https://getbootstrap.com/docs/4.5/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-JcKb8q3iqJ61gNV9KGb8thSsNjpSL0n8PARn9HuZOnIxN0hoP+VmmDGMN5t9UJ0Z" crossorigin="anonymous"> <!-- Favicons --> <link rel="apple-touch-icon" href="images/ppc.png" sizes="180x180"> <link rel="icon" href="images/ppc.png" sizes="32x32" type="image/png"> <link rel="icon" href="images/ppc.png" sizes="16x16" type="image/png"> <meta name="theme-color" content="#28A745"> <style> .bd-placeholder-img { font-size: 1.125rem; text-anchor: middle; -webkit-user-select: none; -moz-user-select: none; -ms-user-select: none; user-select: none; } @media (min-width: 768px) { .bd-placeholder-img-lg { font-size: 3.5rem; } } .cover { background-image: url("images/earth.jpg"); background-size: cover; background-color: rgba(0, 0, 0, .8); background-blend-mode: multiply; } </style> <link href="stylesheets/2kstyle.css" rel="stylesheet" type="text/css"> <link href="stylesheets/archivestyle.css" rel="stylesheet" type="text/css"> <link href="stylesheets/posts.css" rel="stylesheet" type="text/css"> </head> <body style="background-color: black; color: white;"> <nav class="navbar navbar-dark fixed-top green"> <a class="navbar-brand" href="index.html"><b>PPC</b>Planet</a> <button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarsExample09" aria-controls="navbarsExample09" aria-expanded="false" aria-label="Toggle navigation"> <span class="navbar-toggler-icon"></span> </button> <div class="collapse navbar-collapse" id="navbarsExample09"> <ul class="navbar-nav mr-auto "> <li class="nav-item"> <a class="nav-link" href="index.html">Home</span></a> </li> <li class="nav-item active"> <a class="nav-link" href="archive.html">Archive <span class="sr-only">(current)</a> </li> <li class="nav-item"> <a class="nav-link" href="news.html">News</a> </li> <li class="nav-item"> <a class="nav-link" href="contact.html">Contact</a> </li> <li class="nav-item"> <a class="nav-link" href="about.html">About</a> </li> </ul> </div> </nav> <br><br><br><br> <script src="https://www.google.com/recaptcha/api.js"></script> <div class="content home"> <h2 style="color: white;"><b>PPC Planet Public Archive</b></h2> <br> <div id="backDiv"> <a href="deletepost.php"><b>(🗑) Delete or (🚩) report a post</b></a> <br><br> <button id="backDiv" class="greenBtn" onclick="back()">« back</button> <br><br><br> </div> <div id="postsDiv" class="posts content home"></div> <div id="captcha"> <p>To prevent spam and unwanted submissions, we require that you complete the CAPTCHA below.</p> <br> <div class="g-recaptcha brochure__form__captcha" data-sitekey="6Ldku8QZAAAAABQJVhyfOnVljIoUoihUuBUfaFJn" required></div> <br><br><br> <input type="checkbox" id="findCheck" onchange="findToggle()"> <label for="findCheck">Filter Listings</label> <br> <div style="display: none;" id="searchDiv"> <!--text input--> <input type="radio" id="textsearch" name="filters" value="textsearch"> <label for="textsearch">Search by text</label> &nbsp;&nbsp;&nbsp; <input style="width: 75%;" placeholder="Show results that contain inputted text..." type="text" id="searchTxt" /> <br><br> <!--type picker--> <input type="radio" id="typesearch" name="filters" value="typesearch"> <label for="typesearch">Search by type</label> &nbsp;&nbsp;&nbsp; <select name="typeselect" id="typeselect"> <option value="freeware">Freeware</option> <option value="abandonware">Abandonware</option> <option value="self-made">I wrote it myself</option> </select> <br><br> <!--category picker--> <input type="radio" id="categorysearch" name="filters" value="categorysearch"> <label for="categorysearch">Search by category</label> &nbsp;&nbsp;&nbsp; <select name="categoryselect" id="categoryselect"> <option value="app">App</option> <option value="game">Game</option> <option value="driver">Driver</option> <option value="manual">Manual</option> <option value="setup">Setup</option> <option value="ROM">ROM</option> <option value="other">Other</option> </select> </div> <br><br> <button class="greenBtn" onclick="callValidation()">Visit Archive</button> </div> </div> <br><br><br><br> <script> document.getElementById("postsDiv").style.display = "none"; document.getElementById("captcha").style.display = "block"; document.getElementById("searchDiv").style.display = "none"; document.getElementById("backDiv").style.display = "none"; function callValidation() { if (grecaptcha.getResponse().length == 0) { //if CAPTCHA not complete alert('Please complete the CAPTCHA.'); } else { //reset reCAPTCHA and show + hide stuff grecaptcha.reset() document.getElementById("postsDiv").style.display = "block"; document.getElementById("backDiv").style.display = "block"; document.getElementById("captcha").style.display = "none"; //show posts if (document.getElementById("findCheck").checked == true && document.getElementById("typesearch").checked == true) { document.getElementById("searchTxt").value = document.getElementById("typeselect").value; } else if (document.getElementById("findCheck").checked == true && document.getElementById("categorysearch").checked == true) { document.getElementById("searchTxt").value = document.getElementById("categoryselect").value; } //fetch posts from database var posts_search_query = document.getElementById("searchTxt").value; fetch("posts.php?search_query=" + posts_search_query).then(response => response.text()).then(data => { document.querySelector(".posts").innerHTML = data; document.querySelectorAll(".posts .write_post_btn, .posts .reply_post_btn").forEach(element => { element.onclick = event => { event.preventDefault(); document.querySelectorAll(".posts .write_post").forEach(element => element.style.display = 'none'); document.querySelector("div[data-post-id='" + element.getAttribute("data-post-id") + "']").style.display = 'block'; document.querySelector("div[data-post-id='" + element.getAttribute("data-post-id") + "'] input[name='name']").focus(); }; }); document.querySelectorAll(".posts .write_post form").forEach(element => { element.onsubmit = event => { event.preventDefault(); fetch("posts.php?search_query=" + posts_search_query, { method: 'POST', body: new FormData(element) }).then(response => response.text()).then(data => { element.parentElement.innerHTML = data; }); }; }); }); } } function back() { document.getElementById("backDiv").style.display = "none"; document.getElementById("postsDiv").style.display = "none"; document.getElementById("captcha").style.display = "block"; document.getElementById("searchTxt").value = ""; } //when filter toggle changed function findToggle() { if (document.getElementById("findCheck").checked == true) { //when checked document.getElementById("searchDiv").style.display = "block"; document.getElementById("searchTxt").style.display = "block"; document.getElementById("categoryselect").style.display = "block"; document.getElementById("typeselect").style.display = "block"; document.getElementById("textsearch").checked = true; } else { //when unchecked document.getElementById("searchDiv").style.display = "none"; } } </script> <footer class="container center white "> <p>&copy; PPC Planet Team 2020</p> <br> </footer> <script src="https://code.jquery.com/jquery-3.5.1.slim.min.js " integrity="sha384-DfXdz2htPH0lsSSs5nCTpuj/zy4C+OGpamoFVy38MVBnE+IbbVYUew+OrCXaRkfj " crossorigin="anonymous "></script> <script> window.jQuery || document.write('<script src="https://getbootstrap.com/docs/4.5/assets/js/vendor/jquery.slim.min.js "><\/script>') </script> <script src="https://getbootstrap.com/docs/4.5/dist/js/bootstrap.bundle.min.js " integrity="sha384-LtrjvnR4Twt/qOuYxE721u19sVFLVSA4hf/rRt6PrZTmiPltdZcI7q7PXQBYTKyf " crossorigin="anonymous "></script> </body> </html> PHP code: <?php include('mysqlconnect.php'); error_reporting(E_ALL); try { $pdo = new PDO('mysql:host=' . $DATABASE_HOST . ';dbname=' . $DATABASE_NAME . ';charset=utf8', $DATABASE_USER, $DATABASE_PASS); } catch (PDOException $exception) { // If there is an error with the connection, stop the script and display the error exit('Failed to connect to database!' . $exception); } // Below function will convert datetime to time elapsed string function time_elapsed_string($datetime, $full = false) { $now = new DateTime; $ago = new DateTime($datetime); $diff = $now->diff($ago); $diff->w = floor($diff->d / 7); $diff->d -= $diff->w * 7; $string = array('y' => 'year', 'm' => 'month', 'w' => 'week', 'd' => 'day', 'h' => 'hour', 'i' => 'minute', 's' => 'second'); foreach ($string as $k => &$v) { if ($diff->$k) { $v = $diff->$k . ' ' . $v . ($diff->$k > 1 ? 's' : ''); } else { unset($string[$k]); } } if (!$full) $string = array_slice($string, 0, 1); return $string ? implode(', ', $string) . ' ago' : 'just now'; } // This function will populate the posts and posts replies using a loop function show_posts($posts, $parent_id = -1) { $html = ''; if ($parent_id != -1) { // If the posts are replies sort them by the "submit_date" column array_multisort(array_column($posts, 'submit_date'), SORT_ASC, $posts); } $resultCount = 0; // Iterate the posts using the foreach loop foreach ($posts as $post) { if (($_GET['search_query']) != "") { if ($post['parent_id'] == $parent_id) { if (strpos(implode($post), $_GET['search_query'])) { $resultCount++; //check if optional variables are not set $screenshot = $post['screenshot']; if ($screenshot.trim() == "") { $screenshot = "https://ppcplanet.org/images/noscreenshot.png"; } $serial = $post['serial']; if ($serial.trim() == "") { $serial = "n/a"; } $source = $post['source']; if ($source.trim() == "") { $source = "n/a"; } $html .= ' <div class="post"> <br><br> <div> <h3 style="color: white;" class="name"><b>By ' . htmlspecialchars($post['postauthor'], ENT_QUOTES) . '</b></h3> <span class="date">' . time_elapsed_string($post['submit_date']) . '</span> </div> <br> <img class="image" style="width: 256px; height: 256px; overflow: hidden; object-fit: cover;" src=' . nl2br(htmlspecialchars($screenshot, ENT_QUOTES)) . ' alt="Screenshot"/> <br><br> <h2 class="content"><b><a href=' . nl2br(htmlspecialchars($post['url'], ENT_QUOTES)) . ' target="_blank">' . nl2br(htmlspecialchars($post['name'], ENT_QUOTES)) . '</a></b></h2> <br> <p class="content"><b>Description: </b>' . nl2br(htmlspecialchars($post['content'], ENT_QUOTES)) . '</p> <p class="content"><b>Serial: </b>' . nl2br(htmlspecialchars($serial, ENT_QUOTES)) . ' </p> <p class="content"><b>Original Source: </b> <a href =' . nl2br(htmlspecialchars($source, ENT_QUOTES)) . ' target="_blank">' . nl2br(htmlspecialchars($post['source'], ENT_QUOTES)) .'</a></p> <p class="content"><b>Type: </b>' . nl2br(htmlspecialchars($post['type'], ENT_QUOTES)) . ' </p> <p class="content"><b>Category: </b>' . nl2br(htmlspecialchars($post['category'], ENT_QUOTES)) . ' </p> <a class="reply_post_btn" href="#" data-post-id="' . $post['id'] . '">Add on... (ex. another version, manual, etc.)</a> ' . show_write_post_form($post['id']) . ' <div class="replies"> ' . show_posts($posts, $post['id']) . ' </div> </div> <br><br><br> '; ob_clean(); echo(strval($resultCount) . ' result(s) found for "' . $_GET['search_query'] . '"'); //display number of results } } } else { //add each post to HTML variable if ($post['parent_id'] == $parent_id) { //check if optional variables are not set $screenshot = $post['screenshot']; if ($screenshot.trim() == "") { $screenshot = "https://ppcplanet.org/images/noscreenshot.png"; } $serial = $post['serial']; if ($serial.trim() == "") { $serial = "n/a"; } $source = $post['source']; if ($source.trim() == "") { $source = "n/a"; } $html .= ' <div class="post"> <h2></h2> <br><br> <div> <h3 style="color: white;" class="name"><b>By ' . htmlspecialchars($post['postauthor'], ENT_QUOTES) . '</b></h3> <span class="date">' . time_elapsed_string($post['submit_date']) . '</span> </div> <br> <img class="image" style="width: 256px; height: 256px; overflow: hidden; object-fit: cover;" src=' . nl2br(htmlspecialchars($screenshot, ENT_QUOTES)) . ' alt="Screenshot"/> <br><br> <h2 class="content"><b><a href=' . nl2br(htmlspecialchars($post['url'], ENT_QUOTES)) . ' target="_blank">' . nl2br(htmlspecialchars($post['name'], ENT_QUOTES)) . '</a></b></h2> <br> <p class="content"><b>Description: </b>' . nl2br(htmlspecialchars($post['content'], ENT_QUOTES)) . '</p> <p class="content"><b>Serial: </b>' . nl2br(htmlspecialchars($serial, ENT_QUOTES)) . ' </p> <p class="content"><b>Original Source: </b> <a href =' . nl2br(htmlspecialchars($source, ENT_QUOTES)) . ' target="_blank">' . nl2br(htmlspecialchars($post['source'], ENT_QUOTES)) .'</a></p> <p class="content"><b>Type: </b>' . nl2br(htmlspecialchars($post['type'], ENT_QUOTES)) . ' </p> <p class="content"><b>Category: </b>' . nl2br(htmlspecialchars($post['category'], ENT_QUOTES)) . ' </p> <a class="reply_post_btn" href="#" data-post-id="' . $post['id'] . '">Add on... (ex. another version, manual, etc.)</a> ' . show_write_post_form($post['id']) . ' <div class="replies"> ' . show_posts($posts, $post['id']) . ' </div> </div> <br><br><br> '; } } } return $html; } // This function is the template for the write post form function show_write_post_form($parent_id = -1) { $rand = randomIdentifier(); //generate random identifier string $html = ' <div class="write_post" data-post-id="' . $parent_id . '"> <form method="post"> <h2 style="color: white;">New Post</h2> <br> <input name="parent_id" type="hidden" value="' . $parent_id . '"> <label for="name">Title:</label> <input style="width: 100%;" id="name" name="name" type="text" placeholder="Enter a title..." required> <br><br> <label for="screenshot">Screenshot (if applicable):</label> <input style="width: 100%;" id="screenshot" name="screenshot" type="url" placeholder="Screenshot URL"> <br><br> <label for="type">URL:</label> <input style="width: 100%;" id="url" name="url" type="url" placeholder="Download URL" required> <br><br> <label for="type">Description:</label> <textarea name="content" id="content" placeholder="Write a description..." required></textarea> <br><br> <label for="type">Original Source (if known):</label> <input style="width: 100%;" id="source" name="source" type="url" placeholder="Original Source URL"> <br><br> <label for="type">Serial (if applicable):</label> <input style="width: 100%;" id="serial" name="serial" type="text" placeholder="Serial"> <br><br> <label for="name">Your Name/Nickname:</label> <input style="width: 100%;" id="postauthor" name="postauthor" type="text" placeholder="Enter your name..." required> <br><br> <br> <label for="type">Choose a type:</label> <select name="type" id="type"> <option value="freeware">Freeware</option> <option value="abandonware">Abandonware</option> <option value="self-made">I wrote it myself</option> </select> &nbsp;&nbsp;&nbsp; <label for="category">Category:</label> <select name="category" id="category"> <option value="app">App</option> <option value="game">Game</option> <option value="driver">Driver</option> <option value="manual">Manual</option> <option value="setup">Setup</option> <option value="ROM">ROM</option> <option value="other">Other</option> </select> <br><br> <h2 style="color: white;">Post identifier string</h2> <input name="identifier" id="identifier" style="width: 100%;" readonly="true" type="text"" value="' . $rand . '"> <br> <p style="color: red;">This is your post identifier string. It can be used to delete this post in the future without having to contact an admin. <b>Make sure you do not lose it!</b></p> <br><br> <h2 style="color: white;">Make sure your submission meets the following criteria:</h2> <br> <p>🙂 This submission is appropriate and doesn\'t have any mature content. - We want PPC Planet to be a safe place for people of all ages. Inappropriate submissions will be removed!</p> <p>👍 This submission is either freeware, abandonware, or self-made. - No piracy! It\'s not fair to the developer(s).</p> <p>💻 This submission has been tested, and works as advertised. - We don\'t want to have a bunch of broken software on the archive.</p> <p>🧾 This submission is not already on the archive. - Be sure that you are posting something unique!</p> <p>📱 This submission is related to Pocket PCs. - Remember, this is an archive of Pocket PC software.</p> <br> <p><b>By following these rules, we can make the archive a fun (and totally rad) place for everyone!</b></p> <br><br> <p style="color: red; font-size: xx-large; "><b>Make sure you have proofread your post, as you will not be able to edit it once it has been posted. Additionally, make sure you write your down identifier string somewhere if you have not already.</b></p> <br><br> <button type="submit">Create Post</button> <br><br> </form> </div> '; return $html; } if (isset($_GET['search_query'])) { // Check if the submitted form variables exist if (isset($_POST['name'])) { $stmt = $pdo->prepare('INSERT INTO posts (page_id, parent_id, name, screenshot, url, content, serial, type, category, identifier, source, postauthor, submit_date) VALUES (?,?,?,?,?,?,?,?,?,?,?,?,NOW())'); $stmt->execute([ 1, $_POST['parent_id'], $_POST['name'], $_POST['screenshot'], $_POST['url'], $_POST['content'], $_POST['serial'], $_POST['type'], $_POST['category'], $_POST["identifier"], $_POST["source"], $_POST["postauthor"] ]); exit('Your post has been submitted! You can reload the page to see it.'); } // Get all posts by the Page ID ordered by the submit date $stmt = $pdo->prepare('SELECT * FROM posts WHERE page_id = ? ORDER BY submit_date DESC'); $stmt->execute([ 1 ]); $posts = $stmt->fetchAll(PDO::FETCH_ASSOC); // Get the total number of posts $stmt = $pdo->prepare('SELECT COUNT(*) AS total_posts FROM posts WHERE page_id = ?'); $stmt->execute([ 1 ]); $posts_info = $stmt->fetch(PDO::FETCH_ASSOC); } else { exit('No search query specified!'); } function randomIdentifier() { $pass = 0; $complete = false; while (!$complete) { //generate random identifier string until it is unique $alphabet = 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ1234567890!@#$%^&*()'; $pass = array(); $alphaLength = strlen($alphabet) - 1; for ($i = 0; $i < 100; $i++) { $n = rand(0, $alphaLength); $pass[] = $alphabet[$n]; } include('mysqlconnect.php'); $pdo = new PDO('mysql:host=' . $DATABASE_HOST . ';dbname=' . $DATABASE_NAME . ';charset=utf8', $DATABASE_USER, $DATABASE_PASS); $data = implode($pass); $stmt = $pdo->prepare( "SELECT identifier FROM posts WHERE identifier =:id" ); $stmt->bindParam(':id', $data, PDO::PARAM_STR); $stmt->execute(); $myIdentifier = $stmt->fetch(); if (!$myIdentifier) { //identifier is unique $complete = true; } } return $data; } ?> <div class="post_header"> <span style="color: white;" class="total"><?=$posts_info['total_posts']?> total post(s)</span> <a style="color: white;" href="#" class="write_post_btn" data-post-id="-1">Create Post</a> </div> <?=show_write_post_form()?> <?=show_posts($posts)?> How can I fix this so posting works again? All help is appreciated!
  7. I am trying to add a bootstrap class to php echo in mysql query but it doesn't work Here the code that I using $result = $conn->query($sql); echo ""; echo " New Users "; echo " "; echo ""; Any ides ?
  8. wrote a stored procedure this morning and i don’t know how to get the values out of it through a class function in php or phpmyadmin. here is what i wrote : public function totalProcedures($friend_name,$session_id) { /* *query to fetch stored procedure */ try { //executing the stored procedure $sql_sp="CALL timeline (:friend, :session,@updates, @group_posts)"; $stmt_sp= $this->_db->prepare($sql_sp); $stmt_sp->bindValue(":friend",$friend_name); $stmt_sp->bindValue(":session",$session_id); $stmt_sp->execute(); $rows=$stmt_sp->fetch(PDO::FETCH_ASSOC); $stmt_sp->closeCursor(); // closing the stored procedure //trying to get values from OUT parameters. $stmt_sp_2=$this->_db->prepare("select @updates,@group_posts"); $stmt_sp_2->execute(); return $stmt_sp_2->fetch(PDO::FETCH_ASSOC); } catch (PDOException $ei) { echo $ei->getMessage(); } } can someone helpme how to get results. here is the storedprocedure: DELIMITER $$ CREATE DEFINER=`root`@`localhost` PROCEDURE `timeline`(IN `friend` VARCHAR(255), IN `session_id` VARCHAR(255), OUT `updates` VARCHAR(62555), OUT `group_posts` VARCHAR(62555)) BEGIN select * FROM updates where author in (friend,session_id) order by time desc limit 5; select * FROM group_posts where author_gp in (friend,session_id) order by pdate desc limit 5; END$$ DELIMITER ; i get the result in php myadmin as follows: how do i do this inside a php class function. CALL timeline('shan2batman','aboutthecreator', @updates, @group_posts);
  9. Hello, I hope it's ok to ask this question here. I have a registration script, but I'm not sure how to handle it efficiently and I have some questions about it. This is used in the page 'signup.php'. The class is called 'User'. I haven't noticed any errors or bugs. It would be very useful for me to be aware of my mistakes. public function regUser($uname,$upass,$upassverify) { $new_password = password_hash($upass, PASSWORD_DEFAULT); if(!password_verify($upassverify, $new_password)) { // passwords are not the same (I thought it would be better to do this after hashing, but maybe it doesn't matter or it's worse. I'm not sure about it) $info = 'pass_err'; } $stmt1 = $this->db->prepare("SELECT * FROM users WHERE username=:uname"); $stmt1->execute(array(':uname'=>$uname)); if($stmt1->rowCount() > 0) { // this username has already been used $info = 'user_err'; } if (!$info) { $stmt2 = $this->db->prepare("INSERT INTO users(username,password) VALUES(:uname, :upass)"); $stmt2->bindparam(":uname", $uname); $stmt2->bindparam(":upass", $new_password); $stmt2->execute(); // succesfully made an account $info = "success"; } header("Location:/signup.php?status=".$info); exit(); } Am I using the prepared statements as how I should be using them? Is this a safe way of handling my data or do you see vulnerabilities? I'm using PRG to prevent resubmission but I want to show a 'everything is fine' or 'oh no, something went wrong' to the one who is signinup. If I now go to signup.php?status=success, i see 'eveything is fine', without actually signing up, is there a better way to do this or can I somehow prevent everyone being able to see this? As you might have noticed in my last post, my English is not very good, sorry about that. Thanks, Fabian
  10. Hi I have a question about managing data from forms and database, to be exact for safe input/output data from form input fields. Do i need some filters to remove code from input if user try to insert ? When i making database table i limiting chars and same in form. Here is a piece of code i use just for test and example : // connection to database $dbh = new PDO('mysql:host=localhost;dbname=test123', 'root', ''); $dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // variables to insert into database $username = $_POST['username']; $password = $_POST['password']; $email = $_POST['email']; // query with prepare statements $stmt = $dbh->prepare("INSERT INTO members (username, password, email) VALUES (:username, :password, :email)"); $stmt->bindParam(":username", $username, PDO::PARAM_STR); $stmt->bindParam(":password", $password, PDO::PARAM_STR); $stmt->bindParam(":email", $email, PDO::PARAM_STR); $stmt->execute(); $lastId = $dbh->lastInsertId(); // checking if query is passed and data is inserted into dataabse if($lastId > 0) { echo 'Thank u for register.'; } else { echo 'Something went wrong, please try again.'; }
  11. Hi guys, How can i process the value of a search result. this is what i've tried so far: //searche result page if(isset($_POST['submit'])){ $_SESSION['from'] = $_POST['from']; $_SESSION['to'] = $_POST['to']; $sql = ("SELECT * FROM $tbl_name WHERE date_order BETWEEN '$_SESSION[from]' AND '$_SESSION[to]'"); //$stmt = $pdo->prepare("SELECT * FROM ca_processed"); $stmt=$pdo->query($sql); $stmt->execute(); $num_rows = $stmt->rowCount(); #print "<p>$num_rows Record(s) Found.</p>"; if($stmt->rowCount() < 1){ echo '<div class="alert alert-warning text-center">NO RECORD FOUND</div>'; }else{ print "<p>$num_rows Record(s) Found.</p>"; <form action="ReconcileAccounts" method="post"> <table width="100%" class='table-responsive table-condensed table-striped'> <tr> <td bgcolor="#444444"><font color='#fff'></font></td> <td bgcolor="#444444"><font color='#fff'><strong>#</strong></font></td> <td bgcolor="#444444"><font color='#fff'>Trans Ref</font></td> <td bgcolor="#444444"><font color='#fff'>Service Provider</font></td> <td bgcolor="#444444"><font color='#fff'>Service Type</font></td> <td bgcolor="#444444"><font color='#fff'><strong>($) Amount</strong></font></td> <td bgcolor="#444444"><font color='#fff'><strong>Date Paid</strong></font></td> <td bgcolor="#444444"><font color='#fff'><strong>Reconcile Status</strong></font></td> </tr> <?php $i = 1; while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $trans_ref = $row['trans_ref']; $service_provider = $row['service_provider']; $service_type = $row['service_type']; $amount_paid = number_format($row['amount_paid'],2); $date_paid = $row['date_paid']; $reconcile_status = $row['reconcile_status']; if($reconcile_status == 0){ $reconcile_status = "<strong>NOT RECONCILED</strong>"; }elseif($reconcile_status == 1){ $reconcile_status = "<strong>RECONCILED</strong>"; } $reconcile_info = [ 'trans_ref' => $trans_ref, 'service_provider' => $service_provider, 'service_type' => $service_type, 'amount_paid' => $amount_paid, 'date_paid' => $date_paid, 'reconcile_status' => $reconcile_status ]; $_SESSION['reconcile_info'] = $reconcile_info; ?> <tr> <td align="center"><input name="check_list[]" type="checkbox" value="<?php echo $row['id']; ?>" ></td> <td><?php echo $i++; ?></td> <td><?php echo $trans_ref; ?></td> <td><?php echo $service_provider; ?></td> <td><?php echo $service_type; ?></td> <td><?php echo $amount_paid; ?></td> <td><?php echo $date_paid; ?></td> <td><?php echo $reconcile_status; ?></td> </tr> <?php } ?> </table> <input name="reconcile" type="submit" class="btn btn-primary btn-margin" id="reconciled" value="RECONCILE SELECTED"> </form> } } //ReconcileAccounts $tbl_name="xbp_paid_bills"; //your table name $tbl_name2="xbp_registration_info"; if(isset($_POST['reconcile'])){ if(!empty($_POST['check_list'])){ foreach($_POST['check_list'] as $selected){ $stmt = $pdo->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_WARNING ); $stmt = $pdo->prepare("UPDATE xbp_paid_bills SET reconcile_status =1 WHERE trans_id='$selected'"); $stmt->execute(); $count = $stmt->rowCount(); } if($count){ echo "<div class='bg-success alert alert-success text-center'>RECORD(S) RECONCILED</div>"; $url = "ReconcileAccount"; echo '<meta http-equiv="refresh" content="3;URL=' . $url . '">'; }else{ echo "<div class='bg-warning alert alert-warning text-center'>A PROBLEM OCCURED WHILE RECONCILING RECORD</div>"; echo "<br>"; print_r($stmt->errorInfo()); } } } thanks
  12. Hello guys, I'm try to sum rows in a UNION but having a hard time about it $stmt = $pdo->prepare("SELECT due_date, SUM(amount_paid) FROM ( SELECT due_date, amount_paid FROM table1 union all SELECT due_date, amount_paid FROM table2 UNION ALL )x GROUP BY MONTH"); $stmt->execute(); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $row['x']; thanks
  13. I have 5 dropdowns on a tab of a website. I have a database table in MS SQL Server. The table has all the data of 5 dropdowns with one of the fieldNames called Region_Name, say the Region_Names are A, B, C, D, and E. I have written codes to display a table and enabled row editing for one of the RegionNames. Now, I am wondering if I could modify the same codes to display associated table with row editing enabled using different queries when a dropdown is clicked. That could reduce the code repetition and improve the performance. But I do not know how to achieve this. Could anyone please give me some hints? I am using PHP PDO to connect to the database.
  14. I was practicing OOP and made a simple class to log logins. Does anyone see any problems with this or improvements that can be made? Any issue with using NOW() in the query string instead of a placeholder? In another thread, @Jaques1 said: How would I implement that? I rtfm and don't understand it as of yet. <?php // ---------------------------------------------------------------------------- // Database Connection // ---------------------------------------------------------------------------- $dbhost = 'localhost'; $dbname = 'test'; $dbuser = 'root'; $dbpass = ''; $charset = 'utf8'; $dsn = "mysql:host=$dbhost;dbname=$dbname;charset=$charset"; $opt = [PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION, PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC, PDO::ATTR_EMULATE_PREPARES => false, ]; $pdo = new PDO($dsn, $dbuser, $dbpass, $opt); //------------------------------------------------------------------------ // //------------------------------------------------------------------------ $valid_login = new LogLoginStatus($pdo); $valid_login->validLogin('goodusername'); $invalid_login = new LogLoginStatus($pdo); $invalid_login->invalidLogin('bad_username', 'bad_password'); //------------------------------------------------------------------------ // //------------------------------------------------------------------------ class LogLoginStatus { /** * Log Valid/Invalid logins * * @param string login_username * @param string login_password */ public function __construct($pdo) { $this->pdo = $pdo; } function validLogin($username) { $sql = "INSERT INTO user_login (login_status, login_ip, login_username,login_password, login_datetime) values(?, INET_ATON(?), ?, ?, NOW())"; $stmt = $this->pdo->prepare($sql); $stmt->execute(array( 1, $_SERVER['REMOTE_ADDR'], $username, '***' )); } function invalidLogin($username, $password) { $sql = "INSERT INTO user_login (login_status, login_ip, login_username,login_password, login_datetime) values(?, INET_ATON(?), ?, ?, NOW())"; $stmt = $this->pdo->prepare($sql); $stmt->execute(array( 0, $_SERVER['REMOTE_ADDR'], $username, $password )); } } ?> CREATE TABLE `user_login` ( `login_id` int(11) NOT NULL AUTO_INCREMENT, `login_status` tinyint(1) DEFAULT NULL, `login_ip` int(10) unsigned DEFAULT NULL, `login_username` varchar(255) DEFAULT NULL, `login_password` varchar(255) DEFAULT NULL, `login_datetime` datetime DEFAULT NULL, PRIMARY KEY (`login_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
  15. I want to clean up my code by inheriting the database class so I can connect from any class that wants to inherit the database connection. I'm not sure this is the right way but I thought about fixing this by inheriting the __construct function, but how would I call it in this example? Currently I have this; it works, but could this be improved? Or is there a better, cleaner way to do this? $pdo = parent::__construct(); My code: class database { function __construct(){ $servername = "localhost"; $username = "root"; $password = ""; $dbname = "temp"; $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); return $pdo; } } class user extends database { public function getAll(){ $pdo = parent::__construct(); $sql = "SELECT name FROM users"; $stmt = $pdo->prepare($sql); $stmt->execute(); $result = $stmt->fetchAll(PDO::FETCH_ASSOC); return $result; } } $user = new user; $getAllUsers = $user->getAll(); foreach($getAllUsers as $row){ echo $row['name']; }
  16. Hi, Thanks for taking the time. I'm trying PDO for the first time and I'm trying to make a CRUD codebase. I can insert but I have trouble with the select statement and iterate through the data, Apache gives me this error: Fatal error: Uncaught Error: Call to a member function fetch_assoc() on boolean in index.php:38 This is line 38: $getName = $result; while( $row = $getName->fetch_assoc() ){ echo $row['name']; } This is my code: $pdo = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // set the PDO error mode to exception $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $stmt = $pdo->prepare("SELECT name FROM users"); $stmt->execute(); // set the resulting array to associative $result = $stmt->setFetchMode(PDO::FETCH_ASSOC); $getName = $result; //Iterate through the data // line 38: while( $row = $getName->fetch_assoc() ){ echo $row['name']; }
  17. I have been working on a login form, I have completed the registration side but the login form is proving to be fighting back. I have just jumped into the world of PDO and only recently PHP in a serious way. I have been trying to use the password_verify(); function but I have spent so long on it now trying to get it working I have made it more difficult than it should be and probably is. I would be grateful if someone could take a look at my code and just tell me what I am doing wrong. I have tested it with the username and password hard coded in and it returns an array however if I comment out the hard coded username and password I get an empty array. I dare say that someone will see the issue straight away but I cannot get my head round it. <?php session_start(); error_reporting(0); require '../php_inc/connection/connect.php'; require_once '../php_inc/functions.php'; $error = ''; // all error messages will use this variable $msg = 'Please fill in both fields and answer the captcha, they are all required to log in.'; if(isset($_POST['submitted'])){ $dbuname = 'dashby'; // As if check with DB - If I comment these 2 out and try to get data from DB I get empty array $hashed = '$2y$12$7hcyfm7UjboYGaNLF7vK1.qroo3YkvhKAR8EfxG1byEMkNB0oSQgi'; // As if check with DB - same password require 'Captcha.php'; $username = escape_in($_POST['username']); // Username $captcha = escape_in($_POST['captchaResult']); //Captcha $unhashed = escape_in($_POST['password']); //Password b4 hashing takes place //$submittedPassword = password_hash($unhashed, PASSWORD_DEFAULT, ['cost' => 12]); // connect to the database so the checks can be done. if($pdo){ $stmt = $pdo->prepare("select * from users where username = :username && password = :password"); $stmt->bindParam(":username", $username); $stmt->bindParam(":password", $unhashed); // If $hashed is the variable I get an array returned, as $unhashed I get an empty array echo '<pre>'; if($stmt->execute()){ $rows = $stmt->fetchAll(PDO::FETCH_ASSOC); print_r($rows); } } echo '</pre>'; if($total == $getCaptchaResults){ //Capcha OK if(password_verify($unhashed, $hashed)){ //$msg = ''; //$error .= 'Password match'; if($username == $dbuname){ //$msg = ''; //$error .= 'Captcha, username and password ok'; // working to this point $_SESSION['username']; //header('Location: welcomelogged.php'); } else { $msg = ''; $error .= 'Denied wrong username and/or password'; } } else { $msg = ''; $error .= 'Denied wrong password and/or username'; } } else { if(($total != $getCaptchaResults)){ $msg = ''; $error .= 'Captcha Wrong'; } } }// post submitted brace ?> The if statements all work bar the password_verify when I comment out the hard coded variables out, directly under if(isset($_POST['submitted'])) {} I would be grateful if someone could steer me in the right direction. Thanks in advance.
  18. I want to see your opinion about OOP and Procedural. Which method has more easier to code in PHP? I'm using Procedural, but I notice PHP can read OOP as C++. Which is better for PHP? Thanks, Gary
  19. Hello guys. I’m having trouble understanding JOIN and GROUP. I have two tables that I want to merge into one but when I try it, the result is not what is expected and I don’t know where I’m wrong. I want to merge Table A and B to get Table C as shown below: Table A Client Name Username Amount Deposited John Doe joh@doe.com 2500 Julian Cram jul@cram.com 2000 Peter Stalone pet@sta.com 1200 Creig Davies creg@davies.com 3000 Table B Client Name Username Invoice Amount John Doe joh@doe.com 1000 Julian Cram jul@cram.com 500 Peter Stalone pet@sta.com 4500 Creig Davies creg@davies.com 1500 Table C Client Name Username Invoice Amount Amount Deposited John Doe joh@doe.com 1000 2500 Julian Cram jul@cram.com 500 2000 Peter Stalone pet@sta.com 4500 1200 Creig Davies creg@davies.com 1500 3000 echo "<table width='100%' class='table table-striped tbl'>"; echo "<tr> <th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th> <th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th> <th bgcolor='#444444' align='center'><font color='#fff'>Amount Deposited</font></th> </tr>"; $stmt = $pdo->query(" SELECT d.firstname, d.surname, d.username, SUM(d.amt_deposited) AS sum_deposited FROM ca_my_payments d GROUP BY d.username "); while($row = $stmt->fetch(PDO::FETCH_ASSOC)){ echo "<tr><td>"; $client = ucwords($row['firstname'] . " " .$row['surname']); echo $client; echo "</td><td>"; echo $row['username']; echo "</td><td>"; echo $row['sum_deposited']; echo "</tr></td>"; } echo "</table>"; echo "<br><br>"; echo "<table width='100%' class='table table-striped tbl'>"; echo "<tr> <th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th> <th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th> <th bgcolor='#444444' align='center'><font color='#fff'>Invoice</font></th> </tr>"; $stmt = $pdo->query(" SELECT p.payee, p.username, SUM(p.total_payment) AS total_invoice FROM ca_processed p GROUP BY p.username "); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<tr><td>"; echo $row['payee']; echo "</td><td>"; echo $row['username']; echo "</td><td>"; echo number_format($row['total_invoice'],2); echo "</tr></td>"; } echo "</table>"; echo "<br><br>"; echo "<table width='100%' class='table table-striped tbl'>"; echo "<tr> <th bgcolor='#444444' align='center'><font color='#fff'>Client's Name</font></th> <th bgcolor='#444444' align='center'><font color='#fff'>Username</font></th> <th bgcolor='#444444' align='center'><font color='#fff'>Invoice</font></th> <th bgcolor='#444444' align='center'><font color='#fff'>Amount Deposited</font></th> <th bgcolor='#444444' align='center'><font color='#fff'>Current Balance</font></th> </tr>"; $stmt = $pdo->query(" SELECT a.payee, a.username, b.username, SUM(a.total_payment) AS total_invoice, SUM(b.amt_deposited) AS sum_deposited FROM ca_processed a LEFT JOIN ca_my_payments b ON a.username = b.username GROUP BY a.username "); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo "<tr><td>"; echo $row['payee']; echo "</td><td>"; echo $row['username']; echo "</td><td>"; echo number_format($row['total_invoice'],2); echo "</td><td>"; echo number_format($row['sum_deposited'],2); echo "</tr></td>"; } echo "</table>";
  20. I have data on a table1 on one server I need copied onto a table1 on another server that is freshly truncated. I am not getting any error output in the logs or on the screen, but no data ever appears on the second server. mysql replication is banned and no access to cli for mysqldump (this code will be hit numerous times during the day) $pdo = new PDO( 'mysql:host=' . DB_HOST_R2D2 . ';dbname=' . DB_DATABASE_DNS, DB_USER_DNS, DB_PASSWORD ); //yoda pdo settings $pdoyd = new PDO( 'mysql:host=' . DB_HOST_YODA . ';dbname=' . DB_DATABASE_DNS, DB_USER_DNS, DB_PASSWORD ); $pdoyd->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $pdoyd->setAttribute(PDO::ATTR_EMULATE_PREPARES, false); //records table column names $recordstbl = array('id', 'name', 'type', 'content', 'ttl', 'prio', 'change_date', 'disabled', 'ordername', 'auth'); //domain table column names $domainstbl = array('id', 'name', 'master', 'last_check', 'type', 'notified_serial', 'account'); //crypto table column names $cryptotbl = array('id', 'domain_id', 'flags', 'active', 'content'); $tblnames = array('cryptokeys', 'domains', 'records'); //loop through yoda and trunacate all 3 tables foreach($tblnames as $tbl){ $sql = 'truncate '.$tbl; $statementyd = $pdoyd->prepare($sql); $useryd = $statementyd->execute(); var_dump($statementyd); echo '<br>'; } //crazy triple loop to get sql query correct foreach($tblnames as $tbl){ if($tblnames == 'cryptokeys'){ foreach($cryptotbl as $column){ foreach ($column as $pdcolumn){ $pdcolumn = ':'.$pdcolumn; } $insert_stmt = $pdoyd->prepare("INSERT INTO ".$tbl." (".$column.") VALUES (".$pdcolumn." ON DUPLICATE KEY IGNORE"); $select_results = $pdo->query("SELECT * FROM ".$tbl); while ($row = $select_results->fetch(PDO::FETCH_ASSOC)) { $insert_stmt->execute($row); } } } if($tblnames == 'domains'){ foreach($domainstbl as $column){ foreach ($column as $pdcolumn){ $pdcolumn = ':'.$pdcolumn; } $insert_stmt = $pdoyd->prepare("INSERT INTO ".$tbl." (".$column.") VALUES (".$pdcolumn." ON DUPLICATE KEY IGNORE"); $select_results = $pdo->query("SELECT * FROM ".$tbl); while ($row = $select_results->fetch(PDO::FETCH_ASSOC)) { $insert_stmt->execute($row); } } } if($tblnames == 'records'){ foreach($recordstbl as $column){ foreach ($column as $pdcolumn){ $pdcolumn = ':'.$pdcolumn; } $insert_stmt = $pdoyd->prepare("INSERT INTO ".$tbl." (".$column.") VALUES (".$pdcolumn." ON DUPLICATE KEY IGNORE"); $select_results = $pdo->query("SELECT * FROM ".$tbl); while ($row = $select_results->fetch(PDO::FETCH_ASSOC)) { $insert_stmt->execute($row); } } } } logs (source) db1: mysql> select * from mysql.general_log; +---------------------+---------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | +---------------------+---------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ | 2016-01-17 00:34:10 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='nyctelecomm.com' | | 2016-01-17 00:34:10 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and name='nyctelecomm.com' and domain_id=6 | | 2016-01-17 00:34:10 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | select content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='nyctelecomm.com' and domainmetadata.kind='PRESIGNED' | | 2016-01-17 00:34:10 | root[root] @ localhost [127.0.0.1] | 7 | 1 | Query | select cryptokeys.id, flags, active, content from domains, cryptokeys where cryptokeys.domain_id=domains.id and name='nyctelecomm.com' | | 2016-01-17 00:34:11 | root[root] @ localhost [127.0.0.1] | 6 | 1 | Query | select content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='nyctelecomm.com' and domainmetadata.kind='NSEC3PARAM' | | 2016-01-17 00:34:11 | root[root] @ localhost [127.0.0.1] | 6 | 1 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='nyctelecomm.com' | | 2016-01-17 00:34:11 | root[root] @ localhost [127.0.0.1] | 6 | 1 | Query | select min(ordername) from records where ordername > '' and domain_id=6 and disabled=0 and ordername is not null | | 2016-01-17 00:34:11 | root[root] @ localhost [127.0.0.1] | 6 | 1 | Query | select ordername, name from records where ordername <= '' and domain_id=6 and disabled=0 and ordername is not null order by 1 desc limit 1 | | 2016-01-17 00:34:11 | root[root] @ localhost [127.0.0.1] | 6 | 1 | Query | select content from domains, domainmetadata where domainmetadata.domain_id=domains.id and name='nyctelecomm.com' and domainmetadata.kind='SOA-EDIT' | | 2016-01-17 00:34:11 | [powerdns] @ [108.61.175.20] | 420 | 1 | Connect | powerdns@108.61.175.20 on powerdns | | 2016-01-17 00:34:12 | powerdns[powerdns] @ [108.61.175.20] | 420 | 1 | Prepare | SELECT domain_id, name, type FROM records | | 2016-01-17 00:34:12 | powerdns[powerdns] @ [108.61.175.20] | 420 | 1 | Execute | SELECT domain_id, name, type FROM records | | 2016-01-17 00:34:12 | powerdns[powerdns] @ [108.61.175.20] | 420 | 1 | Close stmt | | | 2016-01-17 00:34:12 | powerdns[powerdns] @ [108.61.175.20] | 420 | 1 | Quit | | | 2016-01-17 00:34:13 | [powerdns] @ [108.61.175.20] | 421 | 1 | Connect | powerdns@108.61.175.20 on powerdns | | 2016-01-17 00:34:13 | powerdns[powerdns] @ [108.61.175.20] | 421 | 1 | Quit | | | 2016-01-17 00:34:19 | root[root] @ localhost [] | 411 | 1 | Query | select * from mysql.general_log | +---------------------+---------------------------------------+-----------+-----------+--------------+-------------------------------------------------------------------------------------------------------------------------------------------------------+ 17 rows in set (0.00 sec) logs (target) db2: mysql> select * from mysql.general_log; +---------------------+--------------------------------------------+-----------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | +---------------------+--------------------------------------------+-----------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------+ | 2016-01-17 00:34:15 | powerdns[powerdns] @ localhost [127.0.0.1] | 9 | 2 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='www.zippy-mail.com' | | 2016-01-17 00:34:15 | powerdns[powerdns] @ localhost [127.0.0.1] | 9 | 2 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='zippy-mail.com' | | 2016-01-17 00:34:15 | powerdns[powerdns] @ localhost [127.0.0.1] | 9 | 2 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='com' | | 2016-01-17 00:34:15 | powerdns[powerdns] @ localhost [127.0.0.1] | 9 | 2 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='' | | 2016-01-17 00:34:23 | powerdns[powerdns] @ localhost [127.0.0.1] | 8 | 2 | Query | SELECT content,ttl,prio,type,domain_id,disabled,name,auth FROM records WHERE disabled=0 and type='SOA' and name='nyctelecomm.com' | | 2016-01-17 00:34:25 | [powerdns] @ [108.61.175.20] | 246 | 2 | Connect | powerdns@108.61.175.20 on powerdns | | 2016-01-17 00:34:25 | powerdns[powerdns] @ [108.61.175.20] | 246 | 2 | Prepare | SELECT domain_id, name, type FROM records | | 2016-01-17 00:34:25 | powerdns[powerdns] @ [108.61.175.20] | 246 | 2 | Execute | SELECT domain_id, name, type FROM records | | 2016-01-17 00:34:25 | powerdns[powerdns] @ [108.61.175.20] | 246 | 2 | Close stmt | | | 2016-01-17 00:34:25 | powerdns[powerdns] @ [108.61.175.20] | 246 | 2 | Quit | | | 2016-01-17 00:34:26 | [powerdns] @ [108.61.175.20] | 247 | 2 | Connect | powerdns@108.61.175.20 on powerdns | | 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Prepare | truncate cryptokeys | | 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Execute | truncate cryptokeys | | 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Prepare | truncate domains | | 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Close stmt | | | 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Execute | truncate domains | | 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Prepare | truncate records | | 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Close stmt | | | 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Execute | truncate records | | 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Close stmt | | | 2016-01-17 00:34:26 | powerdns[powerdns] @ [108.61.175.20] | 247 | 2 | Quit | | | 2016-01-17 00:34:41 | root[root] @ localhost [] | 237 | 2 | Query | select id, domain_id, name, type, content from records | | 2016-01-17 00:34:49 | root[root] @ localhost [] | 237 | 2 | Query | select * from mysql.general_log | +---------------------+--------------------------------------------+-----------+-----------+--------------+--------------------------------------------------------------------------------------------------------------------------------------+ 23 rows in set (0.00 sec)
  21. Hello all. I dont know how to go about this. I have a table (Transactions) that contains transactions of users. Another table (Confirmed) contains details of every confirmed user. I want to do a select statement that will display all the confirmed user with only the last of their transaction. But so far all it does is replicate the user and their date of transaction and that is not what i want. My intention is to get something like: Firstname Surname Date Registered Last Transaction andrews john 12-12-2014 10-10-2015 doe andy 12-12-2010 12-12-2014 But i'm getting something like: Firstname Surname Date Registered Last Transaction andrews john 12-12-2014 10-10-2015 andrews john 12-12-2014 10-11-2015 doe andy 12-12-2010 12-12-2014 doe andy 12-12-2010 01-12-2014 doe andy 12-12-2010 12-12-2013 Thanks $stm=$pdo->query("select * from confirmed left join transaction on confirmed.user_id = transaction.user_id where confirmed.status='confirmed' order by date"); while($row = $stmt->fetch(PDO::FETCH_ASSOC)) { echo $row['firstname']; echo $row['surname']; echo $row['regDate']; echo $row['lastTrans']; }
  22. I'm getting the following errors when I run `cat /var/log/apache/error.log` -> PHP Notice: Undefined variable: db_connection in /var/www/html/popreport/includes/inmate.php on line 18 -> PHP Fatal error: Call to a member function query() on a non-object in /var/www/html/popreport/includes/inmate.php on line 18 When I try this in my browser I start with test.php test.php <?php require_once("./database.php"); require_once("./inmate.php"); // foreach($query as $row) // { // print_r($row) . "<br />"; // } $inmate = array(); $inmate = new Inmate($inmate); foreach($inmate as $row) { print $row->firstl_name . "<br />"; } ?> database.php <?php include("./constants.php"); try { $db_connection = new PDO("mysql:host=$host;dbname=$db_name", $db_user, $password); $db_connection->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); } catch (PDOException $e) { print "Error!: " . $e->getMessage() . "<br />"; die(); } ?> inmate.php <?php require_once("./database.php"); class Inmate { private $first_name = ''; private $last_name = ''; private $full_name = ''; private $race = ''; private $number = 0; private $facility = ''; private $type_of_transit = ''; public function __construct($inmate) { $sql = "SELECT * FROM inmate_board"; $query = $db_connection->query($sql); $result = $query->fetch(PDO::FETCH_ASSOC); foreach($result as $row) { $this->$first_name = $result['first_name']; } } public function get_property($property) { return $this->$property; } } ?> In inmate.php I also tried to change the line `$query = $db_connection->query($sql);` to `$query = global $db_connection->query($sql);` but I didn't have any luck here either. Any ideas?
  23. Hi I got 3 tables Table 1 id room pin creator mxitid time Table 2 id roomid user message time mxitid Table 3 id user mxitid room roomid rank kick unid Each room I create I place the new epoch time of when the room expire in Table 1 time field. But now im trying to create a script to check if my current time is bigger than the time in the Table 1 time field and if it is so it should delete the row in table 1, the rows in table 2 with the same roomid as the id of table 1 and the rows in table 3 with the same roomid as the id of table 1 How can I loop through all the records to delete the expired rooms info using PDO mysql?
  24. Not sure why I can't get this been trying to figure it out all evening :/ The included smconfig.php contains my database password. Any ideas, I know it's gotta be easy, I must be missing something. PDO connect.php: <?php session_start(); include 'smconfig.php'; $db_host = "127.0.0.1"; $db_username = "root"; $db_pass = "$dbpass"; $db_name = "golden_wand"; // PDO CONNECT $db = new PDO('mysql:host='.$db_host.';dbname='.$db_name,$db_username,$db_pass); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); ?> http://www.golden-wand.com/members/tester.php <?php include "../Scripts/connect.php"; $email = "test@gmail.com"; $stmt1 = $db->prepare("SELECT id, activated, username, email, password, token FROM members WHERE email=:email LIMIT 1"); $stmt1->bindParam(':email',$email,PDO::PARAM_STR); $stmt1->execute(); $count = $stmt1->rowCount(); if($count > 0){ while($row = $stmt1->fetch(PDO::FETCH_ASSOC)){ $uid = $row['id']; $username = $row['username']; $email = $row['email']; $hash = $row['password']; $activated = $row['activated']; $token = $row['token']; } } echo "Before: <br>"; echo 'UID = '.$uid; echo '<br>Token = '.$token; echo '<br>Activated = '.$activated; echo '<br>Hash = '.$hash; $activated="1"; $token = "md5($hash)"; try{ $db->beginTransaction(); $updateSQL = $db->prepare("UPDATE members SET activated=':activated' WHERE id=':uid' LIMIT 1"); $updateSQL->bindParam(':activated',$activated,PDO::PARAM_STR); $updateSQL->bindParam(':uid',$uid,PDO::PARAM_INT); $updateSQL->execute(); $db->commit(); echo "<br><br><br><br>Update Successful<br><br><br><br>"; } catch(PDOException $e){ $db->rollback(); echo "<br><br><br><br>Update Failed<br><br><br><br>"; } $stmt2 = $db->prepare("SELECT id, activated, username, email, password, token FROM members WHERE email=:email LIMIT 1"); $stmt2->bindParam(':email',$email,PDO::PARAM_STR); $stmt2->execute(); $count = $stmt2->rowCount(); if($count > 0){ while($row = $stmt2->fetch(PDO::FETCH_ASSOC)){ $uid = $row['id']; $username = $row['username']; $email = $row['email']; $hash = $row['password']; $activated = $row['activated']; $token = $row['token']; } } echo "After: <br>"; echo 'UID = '.$uid; echo '<br>Token = '.$token; echo '<br>Activated = '.$activated; echo '<br>Hash = '.$hash; ?> I own this site: http://www.golden-wand.com/phpfreaks.txt
  25. Trying to finish up this PHP section to this login and sign up section. At the moment I'm having issues with the sign up, it's throwing me an error when I signed up, not sure exactly what happened because it did work once and only once. My error throws on line 206 the beginTransaction line. http://www.golden-wand.com/members/contact-test.php try{ $db->beginTransaction(); $ipaddress = getenv('REMOTE_ADDR'); $stmt2 = $db->prepare("INSERT INTO members (firstname, lastname, username, email, password, signup_date, ipaddress) VALUES (:fistname, :lastname, :username, :email, :bcrypt, now(), :ipaddress)"); $stmt2->bindParam(':fistname',$fistname,PDO::PARAM_STR); $stmt2->bindParam(':lastname',$lastname,PDO::PARAM_STR); $stmt2->bindParam(':username',$username,PDO::PARAM_STR); $stmt2->bindParam(':email',$email,PDO::PARAM_STR); $stmt2->bindParam(':bcrypt',$bcrypt,PDO::PARAM_STR); $stmt2->bindParam(':ipaddress',$ipaddress,PDO::PARAM_INT); $stmt2->execute(); /// Get the last id inserted to the db which is now this users id for activation and member folder creation //// $lastId = $db->lastInsertId(); $stmt3 = $db->prepare("INSERT INTO activate (user, token) VALUES ('$lastId', :token)"); $stmt3->bindValue(':token',$token,PDO::PARAM_STR); $stmt3->execute(); // Create our email body $link = 'http://golden-wand.com/Scripts/activate.php?user='.$lastId.'&token='.$token.''; $data = "Thanks for registering an account at Golden Wand! We are glad you decided to join us. Theres just one last step to set up your account. Please click the link below to confirm your identity and get started. If the link below is not active please copy and paste it into your browser address bar. <br><br> $link"; // Create the Transport $transport = Swift_SmtpTransport::newInstance('smtp.gmail.com', 465, 'ssl') ->setUsername($user_name) ->setPassword($pass_word); // Create the Mailer using your created Transport $mailer = Swift_Mailer::newInstance($transport); // Create a message $message = Swift_Message::newInstance('Sign Up') ->setFrom(array('support@golden-wand.com' => 'From: Auto Resposder @ Golden Wand')) ->setTo(array('ditto.100@gmail.com' => 'Recipient')) ->setSubject('IMPORTANT: Activate your Golden Wand Account') ->setBody($data, 'text/html') ; // Send the message $result = $mailer->send($message); $db->commit(); $msg .= "<li class='success'>Thanks for joining! Check your email in a few moments to activate your account so that you may log in. See you on the site!</li>"; $db = null; } catch(PDOException $e){ $db->rollBack(); echo $e->getMessage(); $db = null; } I own this site http://www.golden-wand.com/phpfreaks.txt
×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.