Jump to content

Search the Community

Showing results for tags 'mysql'.

  • 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. I've made a PHP web crawler and then made a MySQL table called "dex" as in index, then I connected to the database through PDO and tweaked the code to "INSERT" websites that aren't already crawled into the table, "UPDATE" for websites that are crawled, and used URL hashes as an indicator or "id" for links. The terminal shows all the links and links related to them, the if statement works perfectly and there are no major errors, so why does it not insert the data into the "dex" table? every-time I check the table after the process I only find the row that I inserted manually to test the if statement for "UPDATE" or "INSERT". what can I do to fix this issue and insert the date the crawler retrieves? Test.html: <a href="https://google.com"></a> <a href="https://www.yahoo.com/"></a> <a href="https://www.bing.com/"></a> <a href="https://duckduckgo.com/"></a> Crawler: <?php error_reporting(E_ALL); ini_set('display_errors', 1); $start = "http://localhost/deepsearch/test.html"; $pdo = new PDO('mysql:host=127.0.0.1;dbname=deepsearch', 'root', ''); $already_crawled = array(); $crawling = array(); function get_details($url) { $options = array('http'=>array('method'=>"GET", 'headers'=>"User-Agent: howBot/0.1\n")); $context = stream_context_create($options); // Suppress warnings for HTML parsing errors libxml_use_internal_errors(true); $doc = new DOMDocument(); @$html = @file_get_contents($url, false, $context); // Load HTML content and check for parsing errors if ($doc->loadHTML($html)) { if (!empty($titleElements)) { $title = $titleElements->item(0); $title = $title->nodeValue; } else { $title = ""; } $description = ""; $keywords = ""; $metas = $doc->getElementsByTagName("meta"); for ($i = 0; $i < $metas->length; $i++) { $meta = $metas->item($i); if ($meta->getAttribute("name") == strtolower("description")) { $description = $meta->getAttribute("content"); } if ($meta->getAttribute("name") == strtolower("keywords")) { $keywords = $meta->getAttribute("content"); } } return '{"Title": "'.str_replace("\n", "", $title).'", "Description": "'.str_replace("\n", "", $description).'", "Keywords": "'.str_replace("\n", "", $keywords).'", "URL": "'.$url.'"}'; } else { // Handle the parsing error echo "HTML parsing error: " . libxml_get_last_error()->message . "\n"; return ''; // Return an empty string or handle the error as needed } } function follow_links($url) { global $pdo; global $already_crawled; global $crawling; $options = array('http' => array('method' => "GET", 'headers' => "User-Agent: howBot/0.1\n")); $context = stream_context_create($options); $doc = new DOMDocument(); @$doc->loadHTML(@file_get_contents($url, false, $context)); $linklist = $doc->getElementsByTagName("a"); foreach ($linklist as $link) { $l = $link->getAttribute("href"); if (substr($l, 0, 1) == "/" && substr($l, 0, 2) != "//") { $l = parse_url($url)["scheme"] . "://" . parse_url($url)["host"] . $l; } else if (substr($l, 0, 2) == "//") { $l = parse_url($url)["scheme"] . ":" . $l; } else if (substr($l, 0, 2) == "./") { $l = parse_url($url)["scheme"] . "://" . parse_url($url)["host"] . dirname(parse_url($url)["path"]) . substr($l, 1); } else if (substr($l, 0, 1) == "#") { $l = parse_url($url)["scheme"] . "://" . parse_url($url)["host"] . parse_url($url)["path"] . $l; } else if (substr($l, 0, 3) == "../") { $l = parse_url($url)["scheme"] . "://" . parse_url($url)["host"] . "/" . $l; } else if (substr($l, 0, 11) == "javascript:") { continue; } else if (substr($l, 0, 5) != "https" && substr($l, 0, 4) != "http") { $l = parse_url($url)["scheme"] . "://" . parse_url($url)["host"] . "/" . $l; } if (!in_array($l, $already_crawled)) { $already_crawled[] = $l; $crawling[] = $l; $details = json_decode(get_details($l)); echo $details->URL . " "; $rows = $pdo->query("SELECT * FROM dex WHERE url_hash='" . md5($details->URL) . "'"); $rows = $rows->fetchColumn(); $params = array(':title' => $details->Title, ':description' => $details->Description, ':keywords' => $details->Keywords, ':url' => $details->URL, ':url_hash' => md5($details->URL)); if ($rows > 0) { echo "UPDATE" . "\n"; } else { if (!is_null($params[':title']) && !is_null($params[':description']) && $params[':title'] != '') { $result = $pdo->prepare("INSERT INTO dex (title, description, keywords, url, url_hash) VALUES (:title, :description, :keywords, :url, :url_hash)"); $result= $result->execute($params); //if ($result) { // echo "Inserted successfully.\n"; //} else { // echo "Insertion failed.\n"; // print_r($stmt->errorInfo()); //} } } //print_r($details)."\n"; //echo get_details($l)."\n"; //echo $l."\n"; } } array_shift($crawling); foreach ($crawling as $site) { follow_links($site); } } follow_links($start); //print_r($already_crawled); ?> at first I tried different links that got me an empty value which resulted in errors and warnings then I changed the links and started writing the "UPDATE", "INSERT" if statement and started specifically writing the insert PDO first to test it out. when I executed the the file using command php I got the intended results in term of how it was supposed to look like in the terminal but then I checked on the table and found out that nothing was inserted. I want to insert these to use them in my search engine and make them searchable by query.
  2. Hello all. please what could be the reason i am not getting the desired result when i do a JOIN. This query gives the desired result //calculate profit $stmt = $pdo->query(" SELECT SUM(total_cost) AS total_cost, SUM(total_sales) AS total_sales FROM tbl_sales WHERE customer_id = 489639 AND status = 0 "); $row = $stmt->fetch(PDO::FETCH_ASSOC); $total_sales = $row['total_sales']; $total_cost = $row['total_cost']; $profit = $total_sales - $total_cost; //calculate balance $stmt = $pdo->query(" SELECT SUM(amt_paid) AS amt_paid FROM tbl_sales_total WHERE customer_id = 489639 AND status = 0 "); $row = $stmt->fetch(PDO::FETCH_ASSOC); $amt_paid = $row['amt_paid']; $balance = $total_sales - $amt_paid; echo "<strong>Total Profit Realised From Customer: ₦".$profit."</strong>"; echo "<br><strong>Total Unpaid Balance: ₦".$unpaid_bal."</strong>"; But when i do a join it gives a wrong result. $stmt = $pdo->query(" SELECT *, SUM(t2.total_price) AS total_sales, SUM(t3.amt_paid) AS amt_paid FROM customers t1 LEFT JOIN tbl_sales t2 ON t1.customer_id = t2.customer_id LEFT JOIN tbl_sales_total t3 ON t1.customer_id = t3.customer_id WHERE t1.customer_id = 489639 AND t3.status = 0 "); WHILE($row = $stmt->fetch(PDO::FETCH_ASSOC)){ echo '<tr> <td>'.$row["customer_name"].'</td> <td>'.$row["total_sales"].'</td> <td>'.$row["amt_paid"].'</td> </td> </tr>'; } Please what did i do wrong and how do i get the desired result. Thanks
  3. Hello guys. I really don't know what to use as a topic and my question might no be clear enough cos I might lack adequate words to put it together but I will try by using examples anyhow. My question is Like during joomla or WordPress installation, the processes one has to undergo e.g filling the company/application name, database name, admin username and password, color scheme etc. In like manner, one has a web app hosted. How to enable different user create their own account and setup their database etc For example I have a web app hosted at https://myapp.com A user can setup their shop on my app https://myapp.com/shop1 or shop1.myapp.com Hope I tried to make it clear enough Thanks
  4. hello all. i dont know if to post this here or javascript/ajax section. if its not the right place, please let me know so i can ask in the right place. i am trying out saving to db via modal form. i learned the basics of using ajax to save data with the modal. so far, i can perform a CRUD via modal but the problem i am having is displaying duplicate entry error inside the modal. I have tried so many ways and the closest i come is displaying error only if a field is duplicate cos i use same trigger as my validation error notice. I'd be glad if i am shown the way to get it to display the error or a better way of getting it done. PS: I want the errors displayed inside the modal. I want the database query error to display where the success message is displayed (i.e on the modalMessage div) Thanks My Modal <div class="modal fade" id="armsModal" data-bs-backdrop="static" tabindex="-1" aria-hidden="true"> <div class="modal-dialog modal-dialog-centered" role="document"> <div class="modal-content"> <div class="modal-header modal-bg"> <h5 class="modal-title w-100 text-center mb-3" id="exampleModalLabel4">Add School Arms</h5> <button type="button" class="btn-close" data-bs-dismiss="modal" aria-label="Close"></button> </div> <form id="submitForm" class="myForm"> <div class="modal-body"> <div id="modalMessage"></div> <div class="mb-3"> <label for="armsname" class="form-label">Arms FullName:</label> <input type="text" class="form-control" id="arms_long_name" name="arms_long_name" autocomplete="off" value="<?php if(isset($_POST['arms_long_name'])){ echo $_POST['arms_long_name']; } ?>"> <span id="longNameError" class="text-danger"></span> </div> <div class="mb-3"> <label for="armsshort" class="form-label">Arms ShortName:</label> <input type="text" class="form-control" id="arms_short_name" name="arms_short_name" autocomplete="off" value="<?php if(isset($_POST['arms_short_name'])){ echo $_POST['arms_short_name']; } ?>"> <span id="shortNameError" class="text-danger"></span> </div> </div> <div class="modal-footer modal-bg"> <button type="button" class="btn btn-outline-light btn-sm" data-bs-dismiss="modal"> Close </button> <button type="submit" class="btn btn-dark btn-sm">Submit</button> </div> </form> </div> </div> </div> My script <script> //Modal $('#submitForm').submit(function(event) { event.preventDefault(); $("#armsModal").on("hidden.bs.modal", function() { $('#longNameError').text(''); $('#shortNameError').text(''); $("#submitForm")[0].reset(); }); $('#armsModal').on('hidden.bs.modal', function () { // Clear form fields $('#submitForm')[0].reset(); // Clear error messages $('.invalid-feedback').text(''); }); // Get form data var formData = { 'arms_long_name': $('#arms_long_name').val(), 'arms_short_name': $('#arms_short_name').val() }; // AJAX request $.ajax({ type: 'POST', url: 'school-arms-action.php', data: formData, dataType: 'json', encode: true }) .done(function(data) { if (!data.success) { if (data.errors.arms_long_name) { $('#longNameError').text(data.errors.arms_long_name); } if (data.errors.arms_short_name) { $('#shortNameError').text(data.errors.arms_short_name); } }else{ modalMessage.innerHTML = '<div class="alert alert-success text-center text-black">ARMS SAVE SUCCESSFUL!</div>'; setTimeout(function() { window.location.href = 'school-arms'; }, 2000); // 2 seconds delay } }); }); </script> My school-arms-action.php $response = array('success' => false, 'errors' => array()); if ($_SERVER['REQUEST_METHOD'] === 'POST') { $arms_long_name = ucwords($_POST['arms_long_name']); $arms_short_name = strtoupper($_POST['arms_short_name']); $arms_id = mt_rand(100, 999); // Validation if (empty($arms_long_name)) { $response['errors']['arms_long_name'] = 'Arms LongName is Required.'; } if (empty($arms_short_name)) { $response['errors']['arms_short_name'] = 'Arms ShortName is Required.'; } // If no errors, proceed to submission if (empty($response['errors'])) { try { $pdo = new PDO("mysql:host=localhost;dbname=db_name", "username", "password"); $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $table=$pdo->query("ALTER TABLE tbl_school_arms AUTO_INCREMENT = 1"); $table->execute(); $stmt = $pdo->prepare(" SELECT * FROM tbl_school_arms WHERE arms_name_long = :arms_name_long OR arms_name_short = :arms_name_short "); $stmt->bindParam(':arms_name_long', $arms_long_name, PDO::PARAM_STR); $stmt->bindParam(':arms_name_short', $arms_short_name, PDO::PARAM_STR); $stmt->execute(); $existingEntry = $stmt->fetch(PDO::FETCH_ASSOC); if ($existingEntry) { //This is what i used but not the right thing i want $response['errors']['arms_long_name'] = 'Duplicate Entry'; } else { // Perform database operations using PDO $stmt = $pdo->prepare(" INSERT INTO tbl_school_arms (arms_id, arms_name_long, arms_name_short) VALUES (:arms_id, :arms_name_long, :arms_name_short)"); $stmt->bindParam(":arms_id", $arms_id); $stmt->bindParam(":arms_name_long", $arms_long_name); $stmt->bindParam(":arms_name_short", $arms_short_name); $stmt->execute(); if($stmt->rowCount()){ $response['success'] = true; } } } catch (PDOException $e) { echo "Error: " . $e->getMessage(); } } } echo json_encode($response);
  5. Hello all, In a column in my table is store an array 58100, 47270, 95437, 52652 which represents in table1 class_id, class_name 58100 JSS 47270 PRY 95437 SSS in table2 subjects, subj_levels English 58100, 47270, 95437 Maths 58100, 47270 Physics 47270, 95437 I have two problems Problem One when i do a select with join, instead of getting JSS, PRY, SSS as result, i am getting only JSS and the other values not showing up. $stmt=$pdo->query(" SELECT t1.subj_name, t1.subj_levels, t2.class_id FROM tbl_school_subjects t1 LEFT JOIN tbl_classes t2 ON t1.subj_levels = t2.class_id "); WHILE($row=$stmt->fetch(PDO::FETCH_ASSOC)){ echo '<tr> <td>'.$row['subj_name'].'</td> <td>'.$row['class_name_small'].'</td> <td>'; } Problem Two when i do a select find_in_set, i get no result. $ids = $_GET['id']; $stmt = $pdo->query(" SELECT * FROM tbl_school_subjects WHERE FIND_IN_SET($ids, subj_levels) > 0 "); what could be the problem? Thanks
  6. For decades I have lain awake at night pondering this major conundrum. Why do PDO::FETCH_BOTH and mysqli->fetch_array() exist? Surely one either wants an associative array or, occasionally, a numerically indexed array? Further, given their uselessness, why the hell are they the default? Can anyone enlighten me as to their raison d'être?
  7. Hello all. I have worked my head out but i cant seem to figure out why i am getting error/entering empty stings. I have tried different things but none seems to work as expected. i know the problem is from the array field validation but cant seem to figure out how to get it done. If i submit without entering any field, the error works fine. But if i fill only one field, it gives error and saves empty strings. Thanks if(isset($_POST['submit'])) { $test_score = $_POST['test_score'][0]; $exam_score = $_POST['exam_score'][0]; if(empty($test_score)) { $error['test_score'] = "Test Score Field is Required"; }if(empty($exam_score)) { $error['exam_score'] = "Exam Score Field is Required"; } if(empty($error)) { foreach ($_POST['subject'] as $key => $value) { $sql = "INSERT INTO $table_name( subject, test_score, exam_score ) VALUES( :subject, :test_score, :exam_score )"; $stmt = $pdo->prepare($sql); $stmt->execute([ 'subject' => $value, 'test_score' => $_POST['test_score'][$key], 'exam_score' => $_POST['exam_score'][$key] ]); } if($stmt->rowCount()){ echo '<div class="alert alert-success text-center">Data Saved</div>'; }else{ echo '<div class="alert alert-danger text-center">Data Not Saved</div>'; } }else{ echo '<br><div class="alert alert-danger text-center">Fields Empty!</div>'; } } //my form <table class="table table-borderless"> <thead> <tr> <th>SN</th> <th>Subject</th> <th>Continuous Assesment Score</th> <th>Examination Score</th> </tr> </thead> <tbody> <div> <form action="" method="post"> <?php $i = 1; $stmt=$pdo->query(" SELECT subjects FROM tbl_subjects_secondary "); WHILE($row = $stmt->fetch(PDO::FETCH_ASSOC)){ ?> <tr> <th><?php echo $i++ ?></th> <td><input type="text" name="subject[]" class="form-control border-0" readonly value="<?php if(isset($row['subject_name'])) { echo $row['subject_name']; } ?>"></td> <td><input type="number" name="test_score[]" class="form-control"><span style="color: red; font-size: .8em;"><?php if(isset($error['test_score'])){ echo $error['test_score'];} ?></span></td> <td><input type="number" name="exam_score[]" class="form-control"><span style="color: red; font-size: .8em;"><?php if(isset($error['exam_score'])){ echo $error['exam_score'];} ?></span></td> </tr> <?php } ?> <tr> <td></td><td></td> <td colspan="2"><button type="submit" name="save" class="btn btn-primary w-50">Save</button></td> </tr> </form> </div> </tbody> </table>
  8. Hi all I am trying to create something like 2023-2024 in a loop so that each year it generate the session for me. I don't want to manually add it. What I did did not work (though I know it won't work but just did it anyway) $cur_yr = date('Y'); $nxt_yr = date('Y', strtotime('+1 year,); $cur_session = $cur_yr."-".$nxt_yr; $strt_session = "2020-2021"; for($i = $strt_session; $i<= $cur_session; $i++){ echo $i; } Mine won't work. But how can I get it done. Thanks in anticipation
  9. Hi there, I was wondering if someone could point me in the right direction please? I am trying to alter my CRUD view table by adding a check box to the row and using a drop down menu to update a field in the database for multiple records. Say that a client has a status of referral and I want to change multiple clients to In Progress, I want to be able to select the clients and use the drop down menu to update the status. Any help would greatly be appreciated. Thanks, ED.
  10. Hi, I running a query that groups merge.Description where the StoreId is the same, and replacing the comma with a line break. The problem I'm getting is the string only returns 1023 character's and I need it to return more? Any help would be great Cheers $rows = mysql_query("SELECT REPLACE(GROUP_CONCAT(merge.Description), ',', CHAR(13)) FROM merge GROUP BY merge.StoreId");
  11. Hi there. I have posts, post comments and users tables which are all fine in and of themselves, but I'd like users to have a unique name that's specific only to the post to hide their real username. This applies to the post itself, and the comments of the post and must remain consistent. For example, if Bill made a post with the name "BigFish", if they comment on their own post then their comments will also have that same name. Likewise, if another user comments with the name "BowlingBall", their subsequent comments will also have the same name. Now, my first attempt was to have an intermediate table of sorts, containing the post_id, user_id and display_name. This takes the appearance of a composite primary key, which are not supported in Laravel. Laravel relationships of such nature can be achieved with a package called Compoships, which does work and given the nature of the table, their resulting query being a gigantic mess of the following is reasonable at best. (post_id == ? AND user_id == ?) OR (post_id == ? AND user_id == ?) OR ... However, that can quickly scale and definitely hurts. Another way to tackle it is to retrieve all display names for each post ID, but that's then retrieving unnecessary data. It's also possible to scrap the table, store the names in the posts and post_comments tables. That's as simple as checking if the commenter is the post author and use that name, or checking if they've previously commented and use that name. I'm not sure if that's ideal either. This brings me back here, after a decade of inactivity, and I do apologise for the lengthy post. How would I go about achieving what I want to do in the best way possible? What would you do in this scenario? Thanks in advance, looking forward to y'all suggestions.
  12. I am creating a real estate management system and I have run into this problem and I have run out of ideas of how to get this things render the info of the house as I intend it to. This page is rendered by listings.js the code of this page is below This is the page where when i click on a house it shoud take me to the next page and renders its info from the database accroding to its corresponding id But I am not getting the info of the house on the next page this is what I am getting on the console I am writing the frontend in reactjs This is listings.js import React, { useState, useEffect } from 'react'; import axios from 'axios'; import { Link } from 'react-router-dom'; import './css/Listings.css'; const Listings = () => { const [houses, setHouses] = useState([]); const [currentPage, setCurrentPage] = useState(1); const [searchTerm, setSearchTerm] = useState(''); // State for search term useEffect(() => { const fetchHouses = async () => { try { const response = await axios.get('http://localhost/api/listings.php'); setHouses(response.data); } catch (error) { console.error('Error fetching houses:', error); } }; fetchHouses(); }, []); const handleNextPage = () => { setCurrentPage(currentPage + 1); }; const handlePrevPage = () => { setCurrentPage(currentPage - 1); }; const startIndex = (currentPage - 1) * 6; const endIndex = startIndex + 6; const handleHouseClick = (id) => { console.log(`House ID: ${id}`); axios .post(`http://localhost:80/api/houseinfo.php?id=${id}`) .then((response) => { console.log('Response:', response.data); }) .catch((error) => { console.error('Error:', error); }); }; const handleSearchTermChange = (event) => { setSearchTerm(event.target.value); }; // Function to handle form submission const handleSubmit = (event) => { event.preventDefault(); console.log('Search Term:', searchTerm); }; return ( <div className="listings-container"> <form className="search-form" onSubmit={handleSubmit}> <input type="text" className="search-input" placeholder="Search by title, location, or type" value={searchTerm} onChange={handleSearchTermChange} /> <button type="submit" className="search-button"> Search </button> </form> <div className="house-grid"> {houses .filter( (house) => house.title.toLowerCase().includes(searchTerm.toLowerCase()) || house.location.toLowerCase().includes(searchTerm.toLowerCase()) || house.type.toLowerCase().includes(searchTerm.toLowerCase()) ) .slice(startIndex, endIndex) .map((house, index) => ( <div key={index} className="house-card"> <Link to={`/houseinfo/${house.id}`} className="house-card-content" onClick={() => handleHouseClick(house.id)} // Pass house id to handleHouseClick > <img src={house.image1} alt={house.title} className="house-image" /> <h2 className="house-title">{house.title}</h2> </Link> </div> ))} </div> <div className="pagination"> {currentPage > 1 && ( <button className="pagination-button" onClick={handlePrevPage}> {'<'} </button> )} <span className="pagination-page">{currentPage}</span> {endIndex < houses.length && ( <button className="pagination-button" onClick={handleNextPage}> {'>'} </button> )} </div> </div> ); }; export default Listings; This is the houseinfo.js import React, { useState, useEffect } from 'react'; import { useParams } from 'react-router' const HouseInfo = ({ match }) => { const [listings, setListings] = useState(null); const {id} = useParams(); useEffect(() => { // Fetch house data from API based on house ID const fetchListings = async () => { try { const response = await fetch(`http://localhost:80/api/houseinfo/${id}`); const data = await response.json(); setListings(data); } catch (error) { console.error('Error fetching house:', error); } }; fetchListings(); }, [id]); if (!listings) { return <div>Loading...</div>; } return ( <div> <h1>House Information</h1> <ul> <li>Title: {listings.title}</li> <li>Location: {listings.location}</li> <li>Price: {listings.price}</li> </ul> </div> ); }; export default HouseInfo; And I am writing backend in php This is houseinfo.php <?php header('Content-Type: application/json'); header('Access-Control-Allow-Origin: *'); header('Access-Control-Allow-Methods: GET'); header('Access-Control-Allow-Headers: Content-Type'); $conn = mysqli_connect('localhost', 'root', '', 'realestate1'); if (!$conn) { die('Failed to connect to MySQL: ' . mysqli_connect_error()); } $houseId = $_GET['id']; $query = "SELECT * FROM listings WHERE id = ?"; $stmt = mysqli_prepare($conn, $query); mysqli_stmt_bind_param($stmt, 'i', $houseId); mysqli_stmt_execute($stmt); $result = mysqli_stmt_get_result($stmt); if (!$result) { die('Failed to fetch house data: ' . mysqli_error($conn)); } if (mysqli_num_rows($result) == 0) { die('House not found'); } $house = mysqli_fetch_assoc($result); $houseJson = json_encode($house); echo $houseJson; mysqli_stmt_close($stmt); mysqli_close($conn); ?> I am getting this on the backend api
  13. I have a delete button that is not working when I click on it it is showing me that error in the console I am using react for my front-end, php for my backend and mysql for my database This is the frontend code import React, { useEffect, useState } from "react"; import "./user.css"; const Buy = () => { const [buyData, setBuyData] = useState([]); const [formData, setFormData] = useState({ id: "", name: "", purpose: "", type: "", area: "", price: "", location: "", status: "", }); const [showForm, setShowForm] = useState(false); // Added state to control form visibility useEffect(() => { fetchBuyData(); }, []); const fetchBuyData = async () => { try { const response = await fetch("http://localhost:80/api/buy.php"); const data = await response.json(); setBuyData(data); } catch (error) { console.error("Error fetching buy data:", error); } }; const handleInputChange = (e) => { const { name, value } = e.target; setFormData({ ...formData, [name]: value }); }; const handleFormSubmit = async (e) => { e.preventDefault(); try { const response = await fetch(`http://localhost:80/api/buy.php`, { method: "PUT", headers: { "Content-Type": "application/json" }, body: JSON.stringify(formData), }); const data = await response.json(); console.log("Data updated successfully:", data); fetchBuyData(); } catch (error) { console.error("Error updating buy data:", error); } }; const handleDeleteClick = async (id) => { try { const response = await fetch( `http://localhost:80/api/buydelete.php?id= `, { method: "DELETE" } ); const data = await response.json(); console.log("Data deleted successfully:", data); fetchBuyData(); } catch (error) { console.error("Error deleting buy data:", error); } }; const handleUpdateClick = (item) => { setFormData({ id: item.id, name: item.name, purpose: item.purpose, type: item.type, area: item.area, price: item.price, location: item.location, status: item.status, }); setShowForm(true); // Show form when update button is clicked }; return ( <div> <h1>Buy Page</h1> <table> <thead> <tr> <th>ID</th> <th>Name</th> <th>Purpose</th> <th>Type</th> <th>Area</th> <th>Price</th> <th>Location</th> <th>Status</th> <th>Actions</th> </tr> </thead> <tbody> {buyData.map((item) => ( <tr key={item.id}> <td>{item.id}</td> <td>{item.name}</td> <td>{item.purpose}</td> <td>{item.type}</td> <td>{item.area}</td> <td>{item.price}</td> <td>{item.location}</td> <td>{item.status}</td> <td> <button onClick={() => handleUpdateClick(item)}>Update</button> <button onClick={() => handleDeleteClick(item.id)}> Delete </button> </td> </tr> ))} </tbody> </table> {/* Form for updating data */} {showForm && ( <form onSubmit={handleFormSubmit}> <h2>Update Data</h2> <label htmlFor="name">Name</label> <input type="text" name="name" value={formData.name} onChange={handleInputChange} /> <label htmlFor="name">Purpose</label> <input type="text" name="purpose" value={formData.purpose} onChange={handleInputChange} /> <label htmlFor="type">Type</label> <input type="text" name="type" value={formData.type} onChange={handleInputChange} /> <label htmlFor="area">Area</label> <input type="text" name="area" value={formData.area} onChange={handleInputChange} /> <label htmlFor="price">Price</label> <input type="text" name="price" value={formData.price} onChange={handleInputChange} /> <label htmlFor="location">Location</label> <input type="text" name="location" value={formData.location} onChange={handleInputChange} /> <label htmlFor="status">Status</label> <input type="text" name="status" value={formData.status} onChange={handleInputChange} /> <button type="submit">Submit</button> </form> )} </div> ); }; export default Buy; And this is the back-end code <?php header('Access-Control-Allow-Origin: http://localhost:3000'); header('Access-Control-Allow-Methods: DELETE'); header('Access-Control-Allow-Headers: Content-Type'); if(isset($_POST['id'])) { $id = $_POST['id']; $conn = new mysqli('localhost', 'root', '', 'realestate1'); if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $stmt = $conn->prepare("DELETE FROM property-buy WHERE id = $id"); $stmt->bind_param("i", $id); if ($stmt->execute()) { echo "Data deleted successfully"; } else { echo "Error deleting data: " . $stmt->error; } $stmt->close(); $conn->close(); } else { echo "Error: 'id' parameter is missing in the request."; } ?> And this is my database What is the problem?
  14. I have created a the frontend fo the registration page in react and wrote backend in php and I have used mysql as my database This is my frontend code import React, { useState } from "react"; import "./css/Register.css"; import { Link } from "react-router-dom"; import axios from "axios"; function Register() { const [firstName, setFirstName] = useState(""); const [lastName, setLastName] = useState(""); const [email, setEmail] = useState(""); const [mobileNumber, setMobileNumber] = useState(""); const [password, setPassword] = useState(""); const [confirmPassword, setConfirmPassword] = useState(""); const [validationErrors, setValidationErrors] = useState({ emailError: "", mobileNumberError: "", passwordMatchError: "", }); const handleSubmit = (e) => { e.preventDefault(); // Email validation using regular expression const emailPattern = /^[^\s@]+@[^\s@]+\.[^\s@]+$/; let newErrors = { emailError: "", mobileNumberError: "", passwordMatchError: "", }; if (!emailPattern.test(email)) { newErrors.emailError = "Please enter a valid email address"; } if (mobileNumber.length !== 10) { newErrors.mobileNumberError = "Please enter a valid mobile number"; } if (password !== confirmPassword) { newErrors.passwordMatchError = "Passwords do not match"; } if ( newErrors.emailError || newErrors.mobileNumberError || newErrors.passwordMatchError ) { setValidationErrors(newErrors); } else { setValidationErrors({ emailError: "", mobileNumberError: "", passwordMatchError: "", }); const userData = { firstName: firstName, lastName: lastName, email: email, mobileNumber: mobileNumber, password: password, }; axios.post("http://localhost:80/api/registration.php", userData); console.log(`UserData: ${JSON.stringify(userData)}`); } }; return ( <div className="register-container"> <h1 className="register-heading">Register</h1> <form onSubmit={handleSubmit} className="register-form"> <label className="register-label"> <input type="text" placeholder="First Name" value={firstName} onChange={(e) => setFirstName(e.target.value)} className="register-input" required /> </label> <br /> <label className="register-label"> <input type="text" placeholder="Last Name" value={lastName} onChange={(e) => setLastName(e.target.value)} className="register-input" required /> </label> <br /> <label className="register-label"> <input type="email" placeholder="Email" value={email} onChange={(e) => setEmail(e.target.value)} className="register-input" required /> </label> {validationErrors.emailError && ( <p className="register-error">{validationErrors.emailError}</p> )} <br /> <label className="register-label"> <input type="tel" placeholder="Mobile Number" value={mobileNumber} onChange={(e) => setMobileNumber(e.target.value)} className="register-input" required /> </label> {validationErrors.mobileNumberError && ( <p className="register-error">{validationErrors.mobileNumberError}</p> )} <br /> <label className="register-label"> <input type="password" placeholder="Password" value={password} onChange={(e) => setPassword(e.target.value)} className="register-input" required /> </label> <br /> <label className="register-label"> <input type="password" placeholder="Confirm Password" value={confirmPassword} onChange={(e) => setConfirmPassword(e.target.value)} className="register-input" required /> </label> {validationErrors.passwordMatchError && ( <p className="register-error"> {validationErrors.passwordMatchError} </p> )} <br /> <button type="submit" className="register-button"> Register </button> </form> <p className="register-login-link"> Already have an account? <Link to="/login">Login</Link> </p> </div> ); } export default Register; and this is my backend <?php $servername = "localhost"; $username = "root"; $password = ""; $dbname = "realestate"; header('Access-Control-Allow-Origin: http://localhost:3000'); header('Access-Control-Allow-Methods: GET, POST, PUT, DELETE'); header('Access-Control-Allow-Headers: Content-Type'); $conn = mysqli_connect($servername, $username, $password, $dbname); if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $data = json_decode(file_get_contents('php://input'), true); if (isset($data['name']) && isset($data['email']) && isset($data['mobileNumber']) && isset($data['password'])) { $name = $data['name']; $email = $data['email']; $mobileNumber = $data['mobileNumber']; $password = $data['password']; $stmt = mysqli_prepare($conn, "INSERT INTO `registration` (`name`,`email`,`mobileNumber`,`Password`) VALUES (?, ?, ?, ?)"); mysqli_stmt_bind_param($stmt, "ssss", $name, $email, $mobileNumber, $password); if (mysqli_stmt_execute($stmt)) { $response = array('status' => 'success', 'message' => 'Data stored successfully!'); } else { $response = array('status' => 'error', 'message' => 'Error: ' . mysqli_error($conn)); } mysqli_stmt_close($stmt); mysqli_close($conn); header('Content-Type: application/json'); echo json_encode($response); } ?> This is the screenshot of the table in database What is the error in the code that I have not been able to find please help me because no data is getting inserted in the database please help
  15. In MySQL, I want to create a trigger with AFTER UPDATE triggering event for my "user" table. In next, I have a table named "user_log" which is use to store the modifications that occurred on the parent table "user" after any update commands. So, data in "user_log" table need be as follows: select * from user_log; +--------+---------+----------------------------+---------------+----------------+---------------------+------+ | log_id | user_id | action | old_data | new_data | changed_date | read | +--------+---------+----------------------------+---------------+----------------+---------------------+------+ | 1 | 10 | Changed yyy's name | yyy | xxx | 2022-06-20 14:06:56 | no | | 2 | 10 | Changed xxx's address | No.111, | No.112, | 2022-06-20 19:07:38 | no | | 3 | 10 | Changed xxx's city | Old City Name | New City Name | 2022-06-20 19:07:38 | no | | 4 | 10 | Changed xxx's phone number | 011-5000000 | 011-4000000 | 2022-06-20 19:07:38 | no | +--------+---------+----------------------------+---------------+----------------+---------------------+------+ As you can see from the data in the table above, it will update several columns at once. So I created my triger as follows, and its working for me. DELIMITER $$ DROP TRIGGER IF EXISTS `user_log` ; $$ CREATE TRIGGER `user_log` AFTER UPDATE ON `user` FOR EACH ROW BEGIN IF OLD.name <> NEW.name THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.name <> OLD.name) THEN CONCAT('Changed ', OLD.name, "'s ", 'name') ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN OLD.name ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN NEW.name ELSE '' END ); END IF; IF OLD.address <> NEW.address THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.address <> OLD.address) THEN CONCAT('Changed ', OLD.name, "'s ", 'address') ELSE '' END , CASE WHEN (NEW.address <> OLD.address) THEN OLD.address ELSE '' END , CASE WHEN (NEW.address <> OLD.address) THEN NEW.address ELSE '' END ); END IF; IF OLD.city <> NEW.city THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.city <> OLD.city) THEN CONCAT('Changed ', OLD.name, "'s ", 'city') ELSE '' END , CASE WHEN (NEW.city <> OLD.city) THEN OLD.city ELSE '' END , CASE WHEN (NEW.city <> OLD.city) THEN NEW.city ELSE '' END ); END IF; IF OLD.phone <> NEW.phone THEN INSERT INTO user_log (user_id,action,old_data,new_data) VALUES( NEW.user_id , CASE WHEN (NEW.phone <> OLD.phone) THEN CONCAT('Changed ', OLD.name, "'s ", 'phone number') ELSE '' END , CASE WHEN (NEW.phone <> OLD.phone) THEN OLD.phone ELSE '' END , CASE WHEN (NEW.phone <> OLD.phone) THEN NEW.phone ELSE '' END ); END IF; END$$ DELIMITER ; My problem is, I have a lot more columns in the user table. Like I said, all columns or several of them are updated at once. In that case I have to add a large amount of INSERT query to my trigger. So here I would like to know if there is another suitable way to do this. I also tried it in this way. But its working only for one column. DROP TRIGGER IF EXISTS `user_log`; CREATE TRIGGER IF NOT EXISTS `user_log` AFTER UPDATE ON user FOR EACH ROW INSERT INTO user_log (user_id,action,old_data,new_data) VALUES ( NEW.user_id , CASE WHEN (NEW.name <> OLD.name) THEN CONCAT('Changed ', OLD.name, "'s ", 'name') WHEN (NEW.address <> OLD.address) THEN CONCAT('Changed ', OLD.name, "'s ", 'address') WHEN (NEW.city <> OLD.city) THEN CONCAT('Changed ', OLD.name, "'s ", 'city') WHEN (NEW.phone <> OLD.phone) THEN CONCAT('Changed ', OLD.name, "'s ", 'phone number') ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN OLD.name WHEN (NEW.address <> OLD.address) THEN OLD.address WHEN (NEW.city <> OLD.city) THEN OLD.city WHEN (NEW.phone <> OLD.phone) THEN OLD.phone ELSE '' END , CASE WHEN (NEW.name <> OLD.name) THEN NEW.name WHEN (NEW.address <> OLD.address) THEN NEW.address WHEN (NEW.city <> OLD.city) THEN NEW.city WHEN (NEW.phone <> OLD.phone) THEN NEW.phone ELSE '' END ); Thank you.
  16. 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.
  17. I have two mysql tables. The two tables with the sample data are as follows. select * from stock; +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+ | stock_id | qty_type | qty | item_id | stock_location_id | stock_type_id | purchase_id | created_date | +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+ | 48 | v | 44.00 | 1 | 1 | 1 | 38 | 2022-05-16 14:27:19 | | 49 | v | 8.00 | 263 | 1 | 1 | 38 | 2022-05-16 14:27:19 | | 50 | a | 6.00 | 1 | 1 | 1 | 39 | 2022-05-16 14:30:04 | | 51 | a | 4.00 | 263 | 1 | 1 | 39 | 2022-05-16 14:30:04 | | 56 | a | 28.00 | 1 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 57 | a | 57.00 | 263 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 58 | a | 6.00 | 264 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 59 | a | 19.00 | 301 | 1 | 1 | 41 | 2022-05-16 14:51:59 | | 64 | a | 15.00 | 263 | 1 | 5 | 0 | 2022-05-18 17:23:37 | | 65 | a | 546.00 | 264 | 1 | 5 | 0 | 2022-05-18 17:23:37 | | 66 | a | 15.00 | 263 | 1 | 5 | 0 | 2022-05-18 17:24:21 | | 67 | a | 546.00 | 264 | 1 | 5 | 0 | 2022-05-18 17:24:21 | | 72 | v | 20.00 | 720 | 1 | 1 | 44 | 2022-05-24 09:24:43 | | 73 | v | 2.00 | 729 | 1 | 1 | 44 | 2022-05-24 09:24:43 | +----------+----------+--------+---------+-------------------+---------------+-------------+---------------------+ select * from sales; +----------+---------+----------+-----------+ | sales_id | item_id | quantity | basket_id | +----------+---------+----------+-----------+ | 7 | 1 | 20.00 | 4 | | 8 | 263 | 3.00 | 4 | | 9 | 1 | 2.00 | 5 | | 10 | 263 | 4.00 | 5 | | 11 | 264 | 6.00 | 5 | | 12 | 301 | 1.00 | 5 | +----------+---------+----------+-----------+ By this I want to build up a update query. This should deduct the quantity of the items in the sales table from the stock table. If such a query can be created in mysql it should be updated in ascending order on the stock_id in the stock table. If such an update query can be built, based on the data in the two tables above, I expect the result to be as follows. select stock_id, qty_type, qty, item_id from stock where qty_type = 'a'; +----------+----------+--------+---------+ | stock_id | qty_type | qty | item_id | +----------+----------+--------+---------+ | 50 | a | 0.00 | 1 | -- clear by sales | 51 | a | 0.00 | 263 | -- clear by sales | 56 | a | 12.00 | 1 | -- deduct qty by sales | 57 | a | 54.00 | 263 | -- deduct qty by sales | 58 | a | 0.00 | 264 | -- clear by sales | 59 | a | 18.00 | 301 | -- deduct qty by sales | 64 | a | 15.00 | 263 | | 65 | a | 546.00 | 264 | | 66 | a | 15.00 | 263 | | 67 | a | 546.00 | 264 | +----------+----------+--------+---------+ Any help would be highly appreciated.
  18. I am trying to develop an PHP MySQL database application where edit details is not working.I am new to PHP and doing this with the help of various web resources such as youtube videos, tutorials, similar programs etc .I am able to fetch the data from the database, but when it comes to edit, the data remains the same even after changing.Can anyone suggest the solution of this problem. manage-profile.php <?php session_start(); require('connection.php'); //If your session isn't valid, it returns you to the login screen for protection if(empty($_SESSION['sl_no'])){ header("location:access-denied.php"); } //retrive student details from the student table $result=mysqli_query($con, "SELECT * FROM student WHERE sl_no = '$_SESSION[sl_no]'"); if (mysqli_num_rows($result)<1){ $result = null; } $row = mysqli_fetch_array($result); if($row) { // get data from db $stdId = $row['sl_no']; $stdRoll = $row['roll_no']; $stdName = $row['name']; $stdClass = $row['class']; $stdSex= $row['sex']; } ?> <?php // updating sql query if (isset($_POST['update'])){ $myId = addslashes( $_GET[$id]); $myRoll = addslashes( $_POST['roll_no'] ); $myName = addslashes( $_POST['name'] ); $myClass = addslashes( $_POST['class'] ); $myGender = $_POST['sex']; $sql = mysqli_query($con,"UPDATE student SET roll_no='$myRoll', name='$myName', class='$myClass', sex='$myGender' WHERE sl_no = '$myId'" ); // redirect back to profile header("Location: manage-profile.php"); } ?> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1" /> <title>Student Profile Management</title> <link href="css/student_styles.css" rel="stylesheet" type="text/css" /> <script language="JavaScript" src="js/user.js"> </script> </head> <body bgcolor="#e6e6e6"> <center><b><font color = "black" size="6">Online Voting System</font></b></center><br><br> <div id="page"> <div id="header"> <h2>Manage Profile</h2> <a href="student.php">Home</a> | <a href="vote.php">Current Polls</a> | <a href="manage-profile.php">Manage My Profile</a> | <a href="changepassword.php">Change Password</a>| <a href="logout.php">Logout</a> </div> <div id="container"> <table border="0" width="620" align="center"> <CAPTION><h3>Update Profile</h3></CAPTION> <form action="manage-profile.php?$id=<?php echo $_SESSION['sl_no']; ?>" method="post" onsubmit="return updateProfile(this)"> <table align="center"> <tr><td>Roll Number:</td><td><input type="text" style="background-color:#e8daef; font-weight:regular;" name="roll_no" maxlength="50" value="<?php echo $row["roll_no"]; ?>"></td></tr> <tr><td>Name:</td><td><input type="text" style="background-color:#e8daef; font-weight:regular;" name="Name" maxlength="30" value="<?php echo $row["name"]; ?>"></td></tr> <tr><td>Class:</td><td><select name='sclass' style='background-color:#e8daef; font-weight:regular;' maxlength='10' id='class' required='true'> <option value='HS-1st Year' <?php if($row["class"]=='HS-1st Year') { echo "selected"; } ?> >HS-1st Year</option> <option value='HS-2nd Year' <?php if($row["class"]=='HS-2nd Year') { echo "selected"; } ?> >HS-2nd Year</option> <option value='BA-1st Sem' <?php if($row["class"]=='BA-1st Sem') { echo "selected"; } ?> >BA-1st Sem</option> <option value='BA-3rd Sem' <?php if($row["class"]=='BA-3rd Sem') { echo "selected"; } ?> >BA-3rd Sem</option> <option value='BA-5th Sem' <?php if($row["class"]=='BA-5th Sem') { echo "selected"; } ?> >BA-5th Sem</option> <option value='BCom-1st Sem' <?php if($row["class"]=='BCom-1st Sem') { echo "selected"; } ?> >BCom-1st Sem</option> <option value='BCom-3rd Sem' <?php if($row["class"]=='BCom-3rd Sem') { echo "selected"; } ?> >BCom-3rd Sem</option> <option value='BCom-5th Sem' <?php if($row["class"]=='BCom-5th Sem') { echo "selected"; } ?> >BCom-5th Sem</option> </select> </td></tr> <tr><td>Sex:</td><td> <input type='radio' style='background-color:#e8daef; font-weight:regular;' name='gender' id='male' value='Male' <?php if($row["sex"]=='Male') { echo "checked"; } ?> >Male<br> <input type='radio' style='background-color:#e8daef; font-weight:regular;' name='gender' id='female' value='Female' <?php if($row["sex"]=='Female') { echo "checked"; } ?> >Female<br></td></tr> <tr><td>&nbsp;</td></tr><tr><td><input type="submit" name="update" value="Update Profile"></td></tr> </table> </form> </div> <div id="footer"> <div class="bottom_addr">Student Union Election,Anonymous College</div> </div> </body> </html>
  19. Ok - I have a working query to which I wanted to add one more item from a new table. Thought it was easy but I must be having a bad day. I will post the original working query (now commented out) and the new query with the added item 'driver_num': /* $q = "select a.race_winner, w.wins, substr(a.race_winner, instr(a.race_winner,' ')+1) as last_name, a.race_name, date_format(a.race_date, '%m/%d') as race_date from trk_races a left outer join (select race_winner, count(race_date) as wins from trk_races where race_winner > '' and Season='$selyr' group by race_winner) w on w.race_winner = a.race_winner where a.race_winner > '' and a.Season='$selyr' order by $orderby"; */ $q = "select a.race_winner, w.wins, substr(a.race_winner, instr(a.race_winner,' ')+1) as last_name, a.race_name, date_format(a.race_date, '%m/%d') as race_date, d.driver_num from trk_races a, drivers d left outer join (select race_winner, count(race_date) as wins from trk_races where race_winner > '' and Season='$selyr' group by race_winner) w on w.race_winner = a.race_winner where a.race_winner > '' and a.Season = '$selyr' and d.driver_season = a.Season and a.race_winner = d.driver_name order by $orderby"; The only addition is the "drivers d" in the from clause of the first select along with the additions to the ending where clause that connects the "drivers" table to the "trk_races" table in that select. Here is the error I am getting from this change: Fatal error: Uncaught PDOException: SQLSTATE[42S22]: Column not found: 1054 Unknown column 'a.race_winner' in 'on clause' in /home/albany/public_html/homejg/nascar/nas_show_drivers.php:129 Stack trace: #0 /home/albany/public_html/homejg/nascar/nas_show_drivers.php(129): PDO->query('select a.race_w...') ..... The error column is already being used elsewhere in the query so I can't figure out why I am getting this error.
  20. For the life of me I can not figure out the syntax error. I was thinking it was related to DateTime field. The data comes over as a string from the api, but converting to time doesn't work. The field in the database is DateTime. Any suggestions would be appreciated. The data from the api for reference. array(1) { [0]=> array(3) { ["macAddress"]=> string(17) "FA:F5:C2:98:6F:29" ["lastData"]=> array(27) { ["dateutc"]=> int(1642622880000) ["tempinf"]=> float(77.9) ["humidityin"]=> int(38) ["baromrelin"]=> float(29.956) ["baromabsin"]=> float(29.132) ["tempf"]=> float(77.2) ["battout"]=> int(1) ["humidity"]=> int(47) ["winddir"]=> int(247) ["windspeedmph"]=> float(5.6) ["windgustmph"]=> float(8.1) ["maxdailygust"]=> float(15.9) ["hourlyrainin"]=> int(0) ["eventrainin"]=> int(0) ["dailyrainin"]=> int(0) ["weeklyrainin"]=> int(0) ["monthlyrainin"]=> float(0.52) ["totalrainin"]=> float(48.862) ["solarradiation"]=> float(196.47) ["uv"]=> int(1) ["feelsLike"]=> float(76.83) ["dewPoint"]=> float(55.39) ["feelsLikein"]=> float(77.2) ["dewPointin"]=> float(50.2) ["lastRain"]=> string(24) "2022-01-12T02:50:00.000Z" ["tz"]=> string(15) "America/Chicago" ["date"]=> string(24) "2022-01-19T20:08:00.000Z" } ["info"]=> array(2) { ["name"]=> string(18) "My Weather Station" ["coords"]=> array(5) { ["coords"]=> array(2) { ["lon"]=> float(-197.65635809999999) ["lat"]=> float(38.6587316) } ["address"]=> string(44) "100 Main Street, Anytown, FL 08226, USA" ["location"]=> string(10) "Anytown" ["elevation"]=> float(214.7066497802734) ["geo"]=> array(2) { ["type"]=> string(5) "Point" ["coordinates"]=> array(2) { [0]=> float(-97.65635809999999) [1]=> float(30.6587316) } } } } } } I take the data from the array and assign it to variables. Notice the unsuccessful attempts to convert string to DateTime. <?php $_DateTime = $data[0]['lastData']['date']; //$_OldDateTime = $data[0]['lastData']['date']; //$_dateTime = strtotime($_OldDateTime); $_tempf = $data[0]['lastData']['tempf']; $_feelsLike = $data[0]['lastData']['feelsLike']; $_stationbarometer = $data[0]['lastData']['baromrelin']; $_sealevelbarometer = $data[0]['lastData']['baromabsin']; $_dewpoint = $data[0]['lastData']['dewPoint']; $_humidity = $data[0]['lastData']['humidity']; $_winddir = $data[0]['lastData']['winddir']; $_windspeed = $data[0]['lastData']['windspeedmph']; $_gust = $data[0]['lastData']['windgustmph']; $_maxdailygust = $data[0]['lastData']['maxdailygust']; $_hourlyrainrate = $data[0]['lastData']['hourlyrainin']; $_dailyrain = $data[0]['lastData']['dailyrainin']; $_weeklyrain = $data[0]['lastData']['weeklyrainin']; $_monthlyrain = $data[0]['lastData']['monthlyrainin']; $_totalrain = $data[0]['lastData']['totalrainin']; $_lastRain = $data[0]['lastData']['lastRain']; //$_OldlastRain = $data[0]['lastData']['lastRain']; //$_lastRain = strtotime($_OldlastRain); $_solar = $data[0]['lastData']['solarradiation']; $_battout = $data[0]['lastData']['battout']; // this is where we insert into the database $sql = "INSERT INTO weather_data (datetime, tempf, feelslike, stationbarometer, sealevelbarometer, dewpoint, humidity, winddir, windspeed, gust, maxdailygust, hourlyrainrate, dailyrain, weeklyrain, monthlyrain, totalrain, lastrain, solar, battout) VALUES ( $_DateTime, $_tempf, $_feelsLike, $_stationbarometer, $_sealevelbarometer, $_dewpoint, $_humidity, $_winddir, $_windspeed, $_gust, $_maxdailygust, $_hourlyrainrate, $_dailyrain, $_weeklyrain, $_monthlyrain, $_totalrain, $_lastRain, $_solar, $_battout )"; //mysqli_query($conn, $sql); if (mysqli_query($conn, $sql)) { echo "Success!"; //json_encode(array("statusCode"=>200)); } else { echo "Error: " . $sql . "<br>" . mysqli_error($conn); } mysqli_close($conn); ?> Error message: Error: INSERT INTO weather_data (datetime, tempf, feelslike, stationbarometer, sealevelbarometer, dewpoint, humidity, winddir, windspeed, gust, maxdailygust, hourlyrainrate, dailyrain, weeklyrain, monthlyrain, totalrain, lastrain, solar, battout) VALUES ( 2022-01-24T15:40:00.000Z, 47.7, 47.7, 30.048, 29.224, 45.49, 92, 314, 1.3, 2.2, 8.1, 0, 0.161, 0.161, 0.681, 49.024, 2022-01-24T15:34:00.000Z, 29.48, 1 ) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ':40:00.000Z, 47.7, 47.7, 30.048, 29.224, 45.49, 92, 314, ' at line 6
  21. I have created a form to edit fields in a database table. This is basically what it does sale_stamp : <input type="text" name="sale_stamp" value="<?php echo $sale_stamp;?>" > $sale_stamp = $_POST['sale_stamp']; $sql = "UPDATE mytable SET sale_stamp = :sale_stamp WHERE id = :id"; $stmt=$db->prepare($sql); $stmt->execute(array( ':id' => $id, ':sale_stamp' => $sale_stamp); This works provided there are values entered but some fields may be blank and this fails for numeric fields. The generated sql is UPDATE asset set sale_stamp = '' WHERE id = '17' The table definition allows null, but when I leave the field blank in the table the generated sql has an empty string rather than null. How do I overcome this?
  22. In my DB schema, there are 4 tables and its relationship as shown in the below attached image. My question is how many schools are there according to the data in the school table and how many of those schools belong to provinces, districts and zones, is it possible in one mysql query? Currently I am using 4 separate queries for this and the relevant code is as follows. $sql = "SELECT count(school_id) as schtot FROM sn_school"; $stmt = $pdo->query($sql); $schtot = $stmt->fetchColumn(); $schtot = str_pad($schtot , 3, 0, STR_PAD_LEFT); $sql = "SELECT count(zone_id) as zonetot FROM sn_school GROUP BY zone_id"; $stmt = $pdo->query($sql); $zonetot = $stmt->rowCount(); $zonetot = str_pad($zonetot , 3, 0, STR_PAD_LEFT); $sql = "SELECT COUNT(d.district_id) as districtTot FROM sn_school s JOIN zone z USING(zone_id) JOIN district d ON d.district_id = z.district_id GROUP BY d.district_id"; $stmt = $pdo->query($sql); $districtTot = $stmt->rowCount(); $districtTot = str_pad($districtTot , 3, 0, STR_PAD_LEFT); $sql = "SELECT COUNT(p.province_id) as ptot FROM sn_school s JOIN zone z USING(zone_id) JOIN district d ON d.district_id = z.district_id JOIN district p ON p.province_id = d.province_id GROUP BY p.province_id"; $stmt = $pdo->query($sql); $ptot = $stmt->rowCount(); $ptot = str_pad($ptot , 3, 0, STR_PAD_LEFT);
  23. I have a query I'm using in my CodeIgniter model to fetch the count of listings of products between particular days. What this query is doing is it's taking status_from from logs where the date is during and after the selected date range and taking added_date from listings where the date falls before the from date range picked by the user and calculates it. Once it has retrieved those records, it checks the table for what variable that status holds and does a sum(case when else 0) to get the total count. function get_report(){ $sql = with Y as ( with recursive D (n, day) as ( select 1 as n, '2021-09-25' my_date union select n+1, day + interval 1 day from D where day + interval 1 day < '2021-10-15' ) select * from D ), X as ( select Y.day, l.*, (select status_from from logs where logs.refno = l.refno and logs.logtime >= Y.day order by logs.logtime limit 1) logstat from listings l, Y where l.added_date <= Y.day ), Z as ( select X.day, ifnull(X.logstat,X.status) stat_day, count(*) cnt from X group by X.day, stat_day ) select Z.day, sum(case when Z.stat_day = 'D' then Z.cnt else 0 end ) Draft, sum(case when Z.stat_day = 'A' then Z.cnt else 0 end ) Action, sum(case when Z.stat_day = 'Y' then Z.cnt else 0 end ) Publish, sum(case when Z.stat_day = 'S' then Z.cnt else 0 end ) Sold, sum(case when Z.stat_day = 'L' then Z.cnt else 0 end ) Let from Z group by Z.day order by Z.day; $query = $this->db->query($sql); return $query; } Dbfiddle: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=6789f0517b194b09d235860dc794ea14 Now here as you can see I'm processing my calculations in the sql statement itself, but I want to do these calculations in my php side, basically iterating everytime it encounters Z.stat_day = 'D' then adding 1 to Draft column and same for the other statuses. Current View Class: <?php foreach($data_total as $row ){ $draft = $row->draft ? $row->draft : 0; $publish = $row->publish ? $row->publish : 0; $action = $row->action ? $row->action : 0; $sold = $row->sold ? $row->sold : 0; $let = $row->let ? $row->let : 0; ?> <tr> <td><?= $row->day?></td> <td><?= $draft ?></td> <td><?= $publish ?></td> <td><?= $action ?></td> <td><?= $sold ?></td> <td><?= $let ?></td> </tr> <?php } ?> Basically I want to refractor my PHP code to do the same thing the Mysql statement is doing, but keeping the query simple and all processing in the PHP side for performance reasons.
  24. Currently I have a table that displays a total count of my data values for each source. I'm getting this value after comparing 2 tables 1 is crm_leads with all my product information and 1 is crm_sources with what sources are the products related to. Now this is the output: Now as you can see the total count is shown under each header next to its source. Now these count values are inside a tags which once clicked go to viewall page. Here basically I want to show the data of the item that I had clicked. So for example, if I clicked the 163 under Hot status, it takes me to the view all page and shows me id, source, enquiry_date for all those under status Hot in a table. So basically it should detect the data for which source and which status is clicked and then accordingly make a statement like this? select * from crm_leads where lead_source = '.$source.' and lead_status = '.$status.'; Model Class: function get_statusreport($fdate='',$tdate='') { $this->db->select("l.lead_status,crm_sources.title,count(*) as leadnum,l.enquiry_date,l.sub_status"); $this->db->from($this->table_name." as l"); if($fdate !='') $this->db->where("date(l.added_date) >=",date('Y-m-d',strtotime($fdate))); if($tdate !='') $this->db->where("date(l.added_date) <=",date('Y-m-d',strtotime($tdate))); $this->db->where("lead_status <>",10); $this->db->join("crm_sources ","crm_sources.id= l.lead_source","left"); $this->db->group_by("l.lead_status,crm_sources.title"); $this->db->order_by("leadnum DESC, crm_sources.title ASC,l.lead_status ASC"); $query = $this->db->get(); $results = $query->result_array(); return $results; } Controller Class(leadstatus holds the view for my current table): public function leadstatus($slug='') { $content=''; $content['groupedleads'] = $this->leads_model->get_statusreport($fdate,$tdate); $this->load->view('crm/main',$main); $this->load->view('crm/reports/leadstatus',$content); } public function viewall($slug='') { $content=''; $this->load->view('crm/main',$main); $this->load->view('crm/reports/viewall',$content); } View class: <?php $ls_arr = array(1=>'Open',8=>'Hot',2=>'Closed',3=>'Transacted',4=>'Dead'); foreach($groupedleads as $grplead){ $statuses[] = $status = $ls_arr[$grplead["lead_status"]]; if($grplead["title"] == NULL || $grplead["title"] == '') $grplead["title"] = "Unknown"; if(isset($grplead["title"])) $titles[] = $title = $grplead["title"]; $leaddata[$status][$title] = $grplead["leadnum"]; } if(count($titles) > 0) $titles = array_unique($titles); if(count($statuses) > 0) $statuses = array_unique($statuses); ?> <table> <tr"> <th id="status">Source</th> <?php if(count($statuses) > 0) foreach($statuses as $status){ ?><th id=<?php echo $status; ?>><?php echo $status; ?></th> <?php } ?> <th>Total</th> </tr> <?php if(is_array($titles)) foreach($titles as $title){ ?> <tr> <?php $total = 0; echo "<td>".$title."</td>"; foreach ($statuses as $status) { $num = $leaddata[$status][$title]; echo "<td><a target='_blank' href='".site_url('reports/viewall')."'>".$num."</a></td>"; $total += $num; $sum[$status] += $num; } echo "<td>".$total."</td>"; $grandtotal += $total; ?> </tr> <?php } ?> </table>
  25. Here is the dbfiddle for better understanding, refer this when reading question: https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=0919cacb5d117450168cdc917433a45e I have 2 tables called listings and logs table. The listings table holds a products reference number and it's current status. So suppose if it's status was Publish currently and it's sold later, the status updates to Sold. Here the refno. in this table is unique since the status can change for 1 product. Now I have another table called Logs table, this table records all the status changes that have happened for a particular product(referenced by refno) in a particular timeframe. The initial entry in the listings table is not recorded here, but once it's status is changed, that entry is shown here. Suppose I have the following Listings table('D' => 'Draft', 'A' => 'Action', 'Y' => 'Publish', 'S' => 'Sold', 'N' => 'Let'): INSERT INTO listings VALUES (3, 'Y','2021-05-02','2021-10-02','LP01'), (4, 'A','2021-05-01','2021-05-01','LP02'), (5, 'S','2020-10-01','2020-10-01','LP03'), (6, 'N','2021-05-01','2021-10-06','LP06'), (10, 'D','2021-10-06','2021-10-06','LP05'), (11, 'D','2021-01-01','2021-01-01','LP04'); Here as of now the total count under every status would give: |status_1|c| |:---:|:--:| |Publish|1| |Action|1| |Sold|1| |Left|1| |Draft|2| But if I wanted only the count for entries made in 2020-10-01 it'll show 0 under all statuses except sold, where it'll show 1. Now in this timeframe between 2020-10-01 and today, there have been values entered in listings table as shown above and also for some, the statuses have changed. Status table: INSERT INTO logs VALUES (1, 'Let','Action','2021-06-01','LP01'), (2, 'Action','Draft','2021-10-01','LP01'), (3, 'Draft','Publish','2021-10-02','LP01'), (4, 'Action','Let','2021-10-06','LP06'); What is being shown right now in my listings table is the values after the status change has been made. So now to get the total count on a particular day, I'm having my statement reference the dates from the logs table and respectively subtract the status_to, and add the status_from. Query for this is in the dbfiddle provided above. Here I made it to return data that happened on or before 2021-10-01 and it does not give the right output. Another problem with this query is I cannot return the data for the entries that had initially taken place. For example like I mentioned above the value for the data on 2020-10-01 should show 1 under sold, while 0 under everything else(desired output), but it does not do this since there are no logs made in logs table for when a new entry in initially added. So basically what I want here is to get the initial entries as well with the same entry. If you want an easier explanation for what I'm trying to achieve, please refer to this:
×
×
  • 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.