Jump to content

Coder2345

Members
 View Profile  See their activity

  • Posts

    21

  • Joined

  • Last visited

 Content Type 

Posts posted by Coder2345

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    You keep swapping from one lot of code to another, then just say it works with this? WELL! did you try what I suggested with the problem we are trying to fix with the ajax method????????, cause I really can't tell. OR you know this code works swap with problem code, and change original $con to $conn, if it works problem solved.

     

     

    I'm not switching to any code? I'm letting you know that I have a different file on the same server that works if I use that code. That is the only code for that file. I used it when I first started coding (literally), in order to know if I was able to establish a connection with the server.

     

    I will try what you suggested. I'm just very confused as to why that file works and the one we are discussing doesn't. It's the same database and the same user with same password that has access to it. So it should work. The only difference is the table.

     

    Thank you for the suggestion. Trying it now.

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    Try' localhost' then the servername as servername with 'root' as username, you really should gathered this information from webhost as you set up the database.

     

     

    You see, both localhost and the servername connect when I use this code in a file;

     

     

     

    <html>

    <head>

    <title>Test Code</title>

    </head>

     

    <?php

    $servername = 'localhost'; //www.example.com also works

    $username = 'user_Admin';

    $password = 'password'; //not showing real password

    $dbname = 'my_DB'; //example DB

     

    // Create connection

    $conn = new mysqli($servername, $username, $password, $dbname);

     

    // Check connection

    if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

    }

    echo "Connected successfully";

    ?>

    </html>

     

    "Connected successfully" is the output. So I'm not sure why the same criteria isn't working in this case.

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    If it's saying that it can't connect to the database then either the server, username, or password are wrong. It's nothing to do with the data types or Javascript, it means it can't even connect to the database at all. After the message that it can't connect you're printing the error message from MySQL, so what does that error say?

     

     

    I was able to locate the error logs in cPanel;

     

     

    PHP Warning: mysqli_connect(): (42000/1044): Access denied for user 'user_Admin'@'111.222.333.444' to database 'my_DB' in /home/user/public_html/Example/getuser.php on line 17

     

    PHP Warning: mysqli_error() expects parameter 1 to be mysqli, boolean given in /home/user/public_html/Example/getuser.php on line 19

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    Comment out original sql and use

     

    //$sql="SELECT * FROM ajax_demo WHERE id = '".$q."'";

    $sql="SELECT * FROM ajax_demo";

     

    And add id to record listing

     

    while($row = mysqli_fetch_array($result)) {

    echo "<tr>";

    echo "<td>" . $row['id'] . " - " . $row['FirstName'] . "</td>";

    echo "<td>" . $row['LastName'] . "</td>";

    echo "<td>" . $row['Age'] . "</td>";

    echo "<td>" . $row['Hometown'] . "</td>";

    echo "<td>" . $row['Job'] . "</td>";

    echo "</tr>";

    }

     

    Just see if this shows all records

     

     

    I tried this but it still says it cannot connect. :(

     

    Do you think the data type could be causing it not to work?

     

    id = int(6)

    FirstName = varchar(20)

    LastName = varchar(20)

    Age = int(3)

    Hometown = varchar(100)

    Job = archer(100)

     

    Or could it be to do with the xmlhttp not having a valid object?

     

    Thank for your help so far. It's appreciated.

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    In your original posting I just noticed you have this

    mysqli_select_db($con,"ajax_demo");

    Which database has table user? And column names we discussed earlier, 'my_DB' or 'ajax_demo' as they could use different passwords for access.

     

     

    I just used "ajax_demo" as an example. My actual database is called something different. My table is also called something different.

     

    To illustrate:

     

    database = my_DB

    table = ajax_demo

    column = id

    other columns = FirstName, LastName, Age, Hometown, Job

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted · Edited by Coder2345

    No, that is node.js, that won't run in a browser. That's totally unrelated to what you're doing. You need to understand what's going on, not add random pieces of code. You're trying to use a variable that doesn't exist. "xmlhttp" is not a reserved word in Javascript, it is a variable name in your code. The variable isn't defined. I couldn't guess why you don't have it defined, other than just the fact that it's not defined where you're trying to use it, but if you show all of your code then maybe I can point it out. In the code on the w3schools tutorial they define it here:

     

            if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        } else {
            // code for IE6, IE5
            xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
        }
    Maybe you decided to remove that code, maybe you changed the variable name, maybe it's in a different scope than where you're trying to set the onreadystatechange handler. I can't really guess without seeing your code.

     

     

    I was missing one letter in the code. That solved that error issue.

     

     

    But now it's back to saying "could not connect" when I select a person from the list.

     

    I was advised to use localhost by a staff member from my web host, so maybe this is the issue?

     

     

     

    $con = mysqli_connect('localhost','user_Admin','password','my_DB');

    if (!$con) {

    die('Could not connect: ' . mysqli_error($con));

    }

     

    That is how I attempt to connect at present.

     

    A different PHP file from earlier practice looks like this:

     

     

     

    <?php

    $servername = 'localhost';

    $username = 'user_Admin';

    $password = 'password';

    $dbname = 'my_DB';

     

    // Create connection

    $conn = new mysqli($servername, $username, $password, $dbname);

     

    // Check connection

    if ($conn->connect_error) {

    die("Connection failed: " . $conn->connect_error);

    }

    echo "Connected successfully";

    ?>

     

    This code works, whereas the other one doesn't. Do you think localhost is the issue, as I had originally used www.servername.com instead of localhost when I wrote the code?

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    In general, that variable is out of scope. Maybe you defined it as a local variable somewhere then tried to reference it from a global function, or maybe you're using the wrong name or something in a closure.

     

     

    I can't quite pinpoint the area where this might be occurring.

     

    A quick Google search of "xmlhttp is not defined" lead me to this: https://www.npmjs.com/package/xmlhttprequest

     

    Should I add that code to mine?

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    You should have setup a database with name 'my_DB', with table 'user, field names or column names wil be if correct be 'id' as shown in sql 'WHERE id', this should match exactly the same case (lower or upper) as field names used in column names of table. The remaining column names should match exactly the names referenced here

     

    $row['FirstName']

    $row['LastName']

    $row['Age']

    $row['Hometown']

    $row['Job']

     

    With records of id, FirstName, LastName, etc

    1 Peter Griffin

    2 Lois Griffin

    3 Joseph Swanson

    4 Glenn Quagmire

     

    If none of these exist, or names do not match exactly, it won't work, if 'id' in sql and table column name is 'ID' they are treated as different.

     

     

    Thank you! I understand what you mean.

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

     

    Try checking php page works by setting id value to one that exist, I presume database, table, table column names match what is required and matching id exist to match

    <?php
//$q = intval($_GET['q']);
$q = 1; // set to matching id in table
$con = mysqli_connect('www.example.com','user_Admin','12345-678','my_DB');
if (!$con) {
die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"ajax_demo");
$sql="SELECT * FROM user WHERE id = '".$q."'";
$result = mysqli_query($con,$sql);

echo "<table>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['FirstName'] . "</td>";
echo "<td>" . $row['LastName'] . "</td>";
echo "<td>" . $row['Age'] . "</td>";
echo "<td>" . $row['Hometown'] . "</td>";
echo "<td>" . $row['Job'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>

     

     

    I've tried what you suggested.

     

    "Could not connect:" is the response when a name is selected from the form. The same response occurs when the php page is run in the browser.

     

    Two questions;

     

     

    $sql="SELECT * FROM user WHERE id = '".$q."'";

     

    What is user referring to here? My table? Shouldn't user have quotes around it?

     

     

     

    <select name="users" onchange="showUser(this.value)">

     

    Also, what is users referring to?

     

    Any help would be much appreciated.

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

     

    The html page should have a element with textHint id, and styling for table placed in between the <head>...</head>

    <html>
<head>
//Javascript code
<script>
function showUser(str) {
if (str == "") {
document.getElementById("txtHint").innerHTML = "";
return;
} else { 
if (window.XMLHttpRequest) {
// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp = new XMLHttpRequest();
} else {
// code for IE6, IE5
xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
document.getElementById("txtHint").innerHTML = this.responseText;
}
};
xmlhttp.open("GET","getuser.php?q="+str,true);
xmlhttp.send();
}
}
</script>
<style>
table {
width: 100%;
border-collapse: collapse;
}

table, td, th {
border: 1px solid black;
padding: 5px;
}

th {text-align: left;}
</style>


</head>
<body>

<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Select a person:</option>
<option value="1">Peter Griffin</option>
<option value="2">Lois Griffin</option>
<option value="3">Joseph Swanson</option>
<option value="4">Glenn Quagmire</option>
</select>
</form>
<div id="txtHint">Result from php page should appear here</div>
</body>
</html>

     

     

    Thank you for your input. I'll try what has been suggested.

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    You are only supposed to retrieve table and data not html, head tags, css etc, the css should be placed the file the content/data is returned to.

    This is only required to be returned

    echo "<table>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['FirstName'] . "</td>";
echo "<td>" . $row['LastName'] . "</td>";
echo "<td>" . $row['Age'] . "</td>";
echo "<td>" . $row['Hometown'] . "</td>";
echo "<td>" . $row['Job'] . "</td>";
echo "</tr>";
}
echo "</table>";

     

     

    My apologies, but I don't quite understand what you mean?

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted · Edited by Coder2345

    You should check the response of the HTTP request to see if a PHP error message shows up.

     

    Remember that functions in Javascript are case sensitive. showuser and showUser are two different identifiers.

     

     

    Thanks. Do you mean like this?

     

     

    The server encountered an internal error or misconfiguration and was unable to complete your request.

    Please contact the server administrator, webmaster@example.net and inform them of the time the error occurred, and anything you might have done that may have caused the error.

    More information about this error may be available in the server error log.

    Additionally, a 500 Internal Server Error error was encountered while trying to use an ErrorDocument to handle the request.

     

    I have both set to showUser in the HTML file.

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    I would ignore the Netbeans error, but you must not put variables directly into SQL queries, use prepared statements: http://www.w3schools.com/php/php_mysql_prepared_statements.asp

     

    What errors are you getting when you run the code? "Doesn't work" is not very descriptive.

     

     

    When I run the PHP file and use Developer Tools in the Chrome browser;

     

     

    Failed to load resource: the server responded with a status of 500 (Internal Server Error)

     

    When I run the HTML file and use Developer Tools in the Chrome browser;

     

     

    selectuser.html:45 Uncaught ReferenceError: showuser is not defined(…)onchange @ selectuser.html:45

     

    ^ This occurs when I attempt to select one of the users from the list. Thanks for replying.

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    You have to put the name of your database in mysqli_select_db().

    But you don't even need to call mysqli_select_db() because the database was already selected when calling mysqli_connect()

     

     

    I changed it to the name of my database, but it still doesn't work? Any other suggestions?

     

    When I view the code in Netbeans, the line below states this;

    $q = intval($_GET['q']);

     

    Do not access SuperGlobal $_GET Array Directly... use some filtering functions instead e.g filter_input( ), conditions with_*is( ) functions, etc.).

    Selecting and retrieving Data from a MySQL Database using PHP and AJAX

    in PHP

    Posted

    I'm trying to select data from a MySQL database that is hosted on a webserver. I want to be able to retrieve the data from a table within the database and then illustrate it within a HTML table. There's an example on W3Schools that I've been following, but I'm unable to retrieve the data successfully.

    http://www.w3schools.com/php/php_ajax_database.asp

    Below is the source code: (HTML)

    <html>
<head>
//Javascript code
<script>
function showUser(str) {
if (str == "") {
document.getElementById("txtHint").innerHTML = "";
return;
} else { 
if (window.XMLHttpRequest) {
// code for IE7+, Firefox, Chrome, Opera, Safari
xmlhttp = new XMLHttpRequest();
} else {
// code for IE6, IE5
xmlhttp = new ActiveXObject("Microsoft.XMLHTTP");
}
xmlhttp.onreadystatechange = function() {
if (this.readyState == 4 && this.status == 200) {
document.getElementById("txtHint").innerHTML = this.responseText;
}
};
xmlhttp.open("GET","getuser.php?q="+str,true);
xmlhttp.send();
}
}
</script>

</head>
<body>

<form>
<select name="users" onchange="showUser(this.value)">
<option value="">Select a person:</option>
<option value="1">Peter Griffin</option>
<option value="2">Lois Griffin</option>
<option value="3">Joseph Swanson</option>
<option value="4">Glenn Quagmire</option>
</select>

    PHP File: (getuser.phd)

    <!DOCTYPE html>
<html>
<head>
<style>
table {
width: 100%;
border-collapse: collapse;
}

table, td, th {
border: 1px solid black;
padding: 5px;
}

th {text-align: left;}
</style>
</head>
<body>

<?php
$q = intval($_GET['q']);

$con = mysqli_connect('www.example.com','user_Admin','12345-678','my_DB');
if (!$con) {
die('Could not connect: ' . mysqli_error($con));
}

mysqli_select_db($con,"ajax_demo");
$sql="SELECT * FROM user WHERE id = '".$q."'";
$result = mysqli_query($con,$sql);

echo "<table>
<tr>
<th>Firstname</th>
<th>Lastname</th>
<th>Age</th>
<th>Hometown</th>
<th>Job</th>
</tr>";
while($row = mysqli_fetch_array($result)) {
echo "<tr>";
echo "<td>" . $row['FirstName'] . "</td>";
echo "<td>" . $row['LastName'] . "</td>";
echo "<td>" . $row['Age'] . "</td>";
echo "<td>" . $row['Hometown'] . "</td>";
echo "<td>" . $row['Job'] . "</td>";
echo "</tr>";
}
echo "</table>";
mysqli_close($con);
?>
</body>
</html>

    *MySQL table is attached

    I think the issue might exist from mysqli_select_db($con,"ajax_demo"); onwards inside the PHP file. Should I be referring to the table that contains the data inside the database?

    I have the PHP File hosted on my webserver, so I'm not sure why it won't retrieve that data when a person is selected from the list of options on the HTML page.

    Any help would be much appreciated.

     

     

×
×
  • Create New...