Jump to content

Search the Community

Showing results for tags 'mysql'.



More search options

  • Search By Tags

    Type tags separated by commas.
  • Search By Author

Content Type


Forums

  • W3Schools
    • General
    • Suggestions
    • Critiques
  • HTML Forums
    • HTML/XHTML
    • CSS
  • Browser Scripting
    • JavaScript
    • VBScript
  • Server Scripting
    • Web Servers
    • Version Control
    • SQL
    • ASP
    • PHP
    • .NET
    • ColdFusion
    • Java/JSP/J2EE
    • CGI
  • XML Forums
    • XML
    • XSLT/XSL-FO
    • Schema
    • Web Services
  • Multimedia
    • Multimedia
    • FLASH

Calendars

  • Community Calendar

Group


AIM


MSN


Website URL


ICQ


Yahoo


Jabber


Skype


Location


Interests


Languages

Found 249 results

  1. SELECT JOIN - A Failed Query

    PROBLEM: Turn a two-step SELECT procedure into a one-step procedure. The two-step procedure is 1) Discover with a query to a parent table what other rows in the parent table are related to the queried row. 2) Obtain selected data for the queried row and other related rows from the parent table and a child table that are connected by a FOREIGN KEY. BACKGROUND: I have two tables -- a parent (parent_table) and a child (child_table) table - connected by a valid FOREIGN KEY and an additional table (ref_table) that contains information about the relationship among the rows of both the parent and child tables. Please find below the results of three SHOW CREATE TABLE statements to help you in your understanding of the table structure. In addition, I have included the INSERT statements for the child_table and ref-table. I accidentally destroyed the INSERT statement for the parent_table. This said, the parent and child tables are very similar in structure. DISCLAIMER: Please understand that the problem that I have created is heuristic in nature and is being used to create a prototype for subsequent, more practical use. The PARENT Table parent_table CREATE TABLE `parent_table` ( `id` int(3) NOT NULL DEFAULT '0', `usertype` enum('1','2','3') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '2' COMMENT '1=good, 2=neutral, 3=bad', `username` char(150) CHARACTER SET utf8 COLLATE utf8_bin NOT NULL, PRIMARY KEY (`id`,`usertype`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 The CHILD Table child_table CREATE TABLE `child_table` ( `id` int(3) NOT NULL DEFAULT '0', `usertype` enum('1','2','3') CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL DEFAULT '2' COMMENT '1=good, 2=neutral, 3=bad', `userbio` varchar(500) DEFAULT NULL, KEY `parent` (`id`,`usertype`), CONSTRAINT `child_table_ibfk_1` FOREIGN KEY (`id`, `usertype`) REFERENCES `parent_table` (`id`, `usertype`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO `child_table` (`id`, `usertype`, `userbio`) VALUES ('1', '1', 'I am Roddy.'), ('2', '2', 'I am Beth.'), ('3', '3', 'I am Matt.'), ('4', '3', 'I am Tim.'), ('5', '2', 'I am Tylor.'), ('6', '1', 'I am Liz.'), ('7', '1', 'I am Aldo.'), ('8', '1', 'I am Adzit.'), ('9', '3', 'I am Jason.'), ('10', '3', 'I am David.') The REFERENCE Table ref_table CREATE TABLE `ref_table` ( `ref_id` int(3) NOT NULL DEFAULT '0', `id` int(3) DEFAULT NULL, `ref` int(3) DEFAULT NULL, `count_ref` int(1) DEFAULT NULL, KEY `par_ref` (`ref_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 INSERT INTO ref_table (ref_id,id,ref,count_ref) VALUES ('1','1','2','1'), ('2','1','6','2'), ('3','1','7','3'), ('4','2','6','1'), ('5','2','9','2'), ('6','4','1','1'), ('7','4','2','2'), ('8','4','6','3'), ('9','4','10','4'), ('10','5','6','1'), ('11','6','1','1'), ('12','6','2','2'), ('13','6','9','3'), ('14','7','5','1'), ('15','7','6','2'), ('16','8','1','1'), ('17','9','10','1'), ('18','10','4','1'), ('19','10','6','2'), ('20',10, NULL,'1'); EXPLANATION BY EXAMPLE: Say a user is interested in row 4 of the parent_table. When the database is queried a SELECT JOIN statement looks in the id field of the ref_table and finds four corresponding rows identified by ref_id 6, 7, 8, and 9. With each of these latter rows is associated a different row -- namely, 1, 2, 6, 10. Without further selection is returned all of the information contained in the parent_table and child_table associated with rows 1, 2, 4, 6, and 10. This is what is supposed to happen, but does not. The REJECTED SQL STATEMENT SELECT * FROM parent_table OUTER JOIN child_table ON parent_table.id = child_table.id OUTER JOIN ref_table ON parent_table.id = ref_table.id QUESTION ONE: Does the table structure make sense? Are the constraints properly set? QUESTION TWO: Why is my SQL statement rejected as poorly formatted? This is my first attempt to use the JOIN clause. So, please be as thorough with your answer as possible. I must believe that I have a long road ahead with MySQL and should prepare for it as i move forward. Roddy
  2. change column names of a table

    My question is, if it is possible, to change the name of the column with MYSQL. (not with php) This is my example: mysql> select * from horses; +------------+---------------+-----------------------+---------------------+---------------------+-----------------------------+ | num | name | location | created_day | changed_day | key_value | +------------+---------------+-----------------------+---------------------+---------------------+-----------------------------+ | 1 | Horse Riding | http://www.camping.fr | 2017-03-30 13:40:16 | 2017-10-19 18:14:57 | Horse, Riding, Woods | | 2 | Horseriding77 | http://www.wine.com | 2017-10-27 10:16:59 | 2017-10-27 10:16:59 | Outdoor, Vacances, Test | | 3 | Horsetours45 | http://www.fake.com | 2017-10-23 16:18:17 | 2017-10-23 16:18:17 | Outdoor, Vacances, Test | | 4 | Horsetours57 | http://www.bing.com | 2017-10-23 17:24:32 | 2017-10-23 17:24:32 | Outdoor, Vacances, Test | | 5 | Horseriding48 | http://www.test.com | 2017-10-25 09:05:18 | 2017-10-25 09:05:18 | Outdoor, Vacances, Test | | 6 | Horseriding67 | http://www.google.uk | 2017-10-27 10:11:18 | 2017-10-27 10:11:18 | Outdoor, Vacances, Test | | 7 | Horseriding52 | http://www.google.es | 2017-10-26 17:00:05 | 2017-10-26 17:00:05 | Outdoor, Vacances, Test | | 8 | Horseriding57 | http://www.google.fr | 2017-10-27 12:13:38 | 2017-10-27 12:13:38 | Outdoor, Vacances, Test | +------------+---------------+-----------------------+---------------------+---------------------+-----------------------------+ 8 rows in set (0.03 sec) mysql> the idea is that for example location as a column name is changed by mysql into other names.
  3. MySQL - Columns based on 2 Columns by Row

    I have 2 tables. A table full of items with an ID number and various values (Table A) and a table which works as the basis for an Item comparison using the ItemID's (Table B ) Table A ItemID, Item_Name, Value_1 1, Potato, 50 2, Carrot, 45 3, Broccoli, 35... Table B ComparisonID, ItemID1, ItemID2 1, 1, 3 2, 2 ,3 3, 5, 145 Requirement: I want to return the ItemID, Item Name and Value for each row in Table B like so Results: ItemID1, Item_Name, Value_1, ItemID2, Item_Name, Value_1 1, Potato, 50, 3, Broccoli, 35 2, Carrot, 45, 3, Broccoli, 35 5, Courgette, 102, 145, Banana, 274 I can create the basic MySQL to return each of the columns using a Union query. However I don't think this is the way to go. It's the part where I need to iterate through each row in Table B whilst also using separate ItemID's for separate columns from the same row that I can't get my head around. Table B will only be up to 1000 rows so it doesn't need to be too efficient. Any ideas where to start? Cheers.
  4. Hello folks, I want to hide a text inside a image(users given image using file upload). Thanks in advance
  5. Read Multiple Rows Sequentially

    QUESTION ONE: What is it called when you traverse an entire table in equal multiples of records? (I ask this question, because I have been fraught with frustration in my google searches.) QUESTION TWO: Do you know a routine that I can copy and modify? (This would greatly simply my search.) BACKGROUND: Surely there must be a standard routine for performing the following procedure: 1) Use PHP to read and display the first ten records of a MySQL table. 2) Read and display the next ten records of the same data table. 3) Either return to the previously read ten records and display these or continue on to the following ten unread records and display them. 5) When you have reached the end of the table display only the remaining records. GOAL: Ultimately I would like to read and display 10 records in a list format. At the bottom of the list I would place two buttons. One that displays the previously 10 records and one that displays the next ten unread records. WHAT I HAVE ACHIEVED SO FAR: <?php $col_name = 'item_pubdate'; $result_obj = $mysqli_obj->query("SELECT * FROM rss2_podcast_item ORDER BY '$col_name' LIMIT 0, 10"); while($row=$result_obj->fetch_array()) { ?> <div class='table_row podcast_item'> <div class='flex_item num_div'><?php echo $row['podcast_no_item'] . '&nbsp'; ?></div> <div class='flex_item date_div'><?php echo $row['item_pubdate']; ?></div> <div class='flex_item title_div'><?php echo $row['item_title']; ?></div> </div><!-- end div.table_row --> <div class='table_row discover_div'> <div class='flex-item'>Discover more ...</div> </div><!-- end div.table_row --> <div class='table_row'> <div class='flex-item details_div'> <?php echo $row['item_description']; ?> </div><!-- end div.table_row --> </div><!-- end div.table_row --> <?php } ?> With a little additional CSS and Javascript I have been able to achieve the following display. Clicking on the phrase "Discover more ..." reveals the hidden detail obtained from $row['item_description]. No. Publication Date|Time Podcast Title 60 2017-06-19 17:26:41 Title Ten Discover more ... 59 2017-06-18 09:50:37 Title Nine Discover more ... . . . 51 2017-09-24 11:00:17 Title One Discover more ... DESIRE: If I can only achieve the above stated goal, I can likely figure out a way to get AJAX to allow users to select podcast items by the week, month, year, and later category. But, this is already much too far into the future. For the moment, I would simply like to achieve my above stated goal. Any ideas? Roddy
  6. $text = "i am a student of oxford university, and i love to tell everyone how much i have enjoyed my four years in school. the whole environment was really welcoming" . Actually, we have a database table called "text_word". So, i want a script that will search through the above text and check if any of those words from the above text does not exist in the "text_word" table, such word should be replaced by asterisks and store the replaced word into a separate column called "unmatched_word" in the same "text_word" table. let's assume the word "welcoming" was not existing in my database what i want to achieve is this. the text is parsed through the program INPUT: "i am a student of oxford university, and i love to tell everyone how much i have enjoyed my four years in school. the whole environment was really welcoming" . OUTPUT: "i am a student of oxford university, and i love to tell everyone how much i have enjoyed my four years in school. the whole environment was really ******" . Code Sample: foreach ($text as $word) { $select_word = $f->filterRecord("*", "parts_of_speech", "word", $word); $get_word = $f->show_data($select_word); $count_words = $f->numR($select_word); if ($count_words > 0) { $result[] = '<a href="word/?w='.$word.'">'.$word.'</a>'; } else { $result[] = "******"; } } Please i believe the code is not doing exactly what i want. If you have a better approach to how i can get the job done, i don't mind if you give me a fresh code snippet or script. I hope you understand what i'm trying to do now?
  7. Unwanted Row Entry Duplication

    QUESTION ONE: How does one suppress duplicate row entries? QUESTION TWO: In the following case what is causing them to occur? BACKGROUND: I created several tables only to discover that they were not as I wanted them. AFter truncateng each, I dropped what I believed to be poorly structured columns, rearranged some, and added still others. When I tested the result, everything worked great except for one problem -- a single table entry reuslted in duplicate rows. THE TABLE STRUCTURE CREATE TABLE 'table_name' (obs` int(5) NOT NULL AUTO_INCREMENT . . . PRIMARY KEY (`obs`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 A SAMPLE SQL STATEMENT INSERT INTO rss2_podcast_itunes (podcast_no_itunes, itunes_category1, itunes_category2, itunes_category3, itunes_complete, itunes_new_feed_url, itunes_name, itunes_email, itunes_duration, itunes_order, itunes_author, itunes_block, itunes_image, itunes_explicit, itunes_summary) VALUES ('54', 'education', 'language', 'English', '', '', 'Roddy A. Stegemann', 'kiusau@me.com', '00:01:01', '', 'Roddy A. Stegemann', '', 'http://www.grammarcaptive.com/_images/captive.png', '', 'Test') Please note the following: No value is entered for the obs field/column. The auto increment advances one for each row entered. Below each successfully loaded row entry appears another row identical in every way but one -- the appearance of an incremental advance in the value of the obs field. Please advise. Roddy
  8. 1) I have login scripts working in combination with database table and verify_password 2) the login scripts are RE-USED for another site 3) In the test fase I use the SAME database table values and copy these in phpmyadmin to a new table is it possible there is some kind of copy security or whatever in the encrypted original table values? I presume this because there is no password_verify match. It could be I'm overlooking something, but the password_verify($form_pw, $database_pw) procedure, does not need encryption BEFORE $form_pw is read? The only conclusion then is: the value in the newly copied table is not the same is in the original table. (of course I can also reinstall the registration scripts to test it, but i would like to rule out other causes ) EDIT: solved. silly me using the wrong password
  9. Hey guys, I have a problem. I want to write a record in my db when it does not already exists. For this, i have been looking on the web and found multiple solutions on Stackoverflow. The one I use is proved working, by the stack user however I get my die response :(. I use the following query (mysql/php): $query= "IF NOT EXISTS (SELECT * FROM tasksmade WHERE teamnumber='$teamnumber' AND tasknumber='$tasknumber' AND date='$date') INSERT INTO tasksmade (teamnumber, tasknumber, taskfinished, date) VALUES ('$teamnumber', '$tasknumber', '$taskfinished', '$date')"; $result= mysql_query($query,$mysql) or die("The query result has failed on the db!"); Hope someone can help! Thanks!
  10. mysql join from 2 different databases

    Hi all, I have a question about join or inner join; not sure in what way it should be used. I now use 2 queries in 2 different databases (made in phpmyadmin). SELECT `title` FROM `writers` WHERE id ='qwert58efedd1979f'; SELECT `name`, `lastname`, `str`, `nr`,` place` FROM `client` WHERE id ='qwert58efedd1979f'; I would like to make one mysql query and use join to search in 2 tables in 2 different databases. Can anyone tell how mysql does this?
  11. Xampp is not working

    Hello folks, I installed a XAMPP Version 7.0.15 on my windows 7(32-bit) computer but it doesn't work properly.When i open in the browser it shows "The site can't be reached". What's the solution for it? please help me. Thank you in Advance..
  12. Copy table from sql to mdb

    Hi, i'm new and i hope not wrong with to ask the following question: i see this instruction SELECT * INTO CustomersBackup2013 IN 'Backup.mdb' FROM Customers; in thee site, that is in a section of copy from sql (to mdb, i thougt since the db name is backup.mdb). I'm looking for the similar command for copy/create the similar command but for mysql as origin and mdb as destination. Thanks of all.
  13. wordpress, mybbs, wiki.

    is there a way to make a singular login for a wordpress website that also logs a person into the forums and the wiki at the same time?
  14. connecting to msqli

    Looking at how form items are put into a mysql table, the part that seems daunting is if ones form has like 50 items... having to list all those IDs with their variables makes me wonder if there is a better way or if its just " it up buttercup.".
  15. So my question is: how can I insert variables into my mysql queries in php? This is my code now, I would like to use the $me and the $friend variables to work in the sql query: function newChat($me, $friend) { global $conn; $sql = "SELECT id FROM chats WHERE (person1 = $me AND person2 = $friend) OR (person1 = $friend AND person2 = $me);"; mysqli_query($conn, $sql) or die('Error querying database.'); echo "check <br />"; $result = mysqli_query($conn, $sql); $row = mysqli_fetch_array($result); while ($row = mysqli_fetch_array($result)) { echo $row['id'] . ' - ' . $row['person1'] . ' - ' . $row['person2'] . ' - ' . $row['date'] .'<br />'; } } newChat('John', 'Marie'); Also if I set $sql to "SELECT * FROM chats" it only gives me one row of my two test rowes in my database. (it only gives the second row, and just ignores the first one? --> a screenshot of the database is at the end of this post) Can anyone please help me with this? Thanks already!
  16. updating column with increment in mysql

    In phpmyadmin I did the following query and had it output in php like this: $sql = "UPDATE `table` SET count = count + 1 WHERE unique_nr = 5175781"; this all worked well. though when I tried to implement it in my script I had an error: script: <?php // code to establish dbase connection: var_dump($unique_nr); // $stmt = $conn->prepare('UPDATE table SET count = ? WHERE unique_nr = ? '); $stmt = $conn->prepare('UPDATE table SET count = count + 1 WHERE unique_nr = ? '); $stmt->bind_param("is", $count, $unique_nr); $stmt->execute(); ?> resulting in an error message. I also tried this way of writing for bind_param: $stmt->bind_param("s", $unique_nr); but that didnt update the column, though there wasnt an error message anymore
  17. MySQL query is not working

    MySQL queries are not working only inside this block: ... if (isset($_POST['send'])) { } ... The variable itself is set and transferred from the form. What could be the reasons of this and how is better to check?
  18. InnoDB for Image Gallery Tables

    Dear all, For photo gallery tables (named: gallery_album, gallery_image, gallery_relation), I have seen a recommendation to use InnoDB engine in preference to MyISAM. I thought MyISAM would be the choice for all tables but tables for transactions. Do you agree with the recommendation to using InnoDB for photo gallery tables and why?
  19. Index vs Key

    Dear all, I am trying to dig into the exact difference between key and index in MySQL tables. Some say they are synonymous, others give some differences. I still don't feel confident enough to decide when to use each on a table. I would appreciate if someone well-versed in their difference explain such difference and when to use each. Thanks.
  20. Different Image Size

    Hi all, I have uploaded an image to a folder/directory and stored its size using the $_FILES['image']['size'] superglobal. While the image source size was reading 153kb, the stored size is 157317. Why there is a difference in size?
  21. Why Do We Store tmp_name of a File?

    Dear all. For an image uploaded to a folder/directory and stored its name, type, and size in a DB table, what could be the merits of also storing its temporary name, noting that such temporary name is deleted by the server upon moving the image to the desired folder/directory?
  22. Unknown Printed Charector

    Hi all, In fetching a result from a table (below code), 1 is also printed after the fetched record (fetched record below). There is no 1 in the table, nor have I accidentally included it in my script. What is it and why does it appear? <?php require 'db/connect.php'; if($result = $db->query("SELECT * FROM user")) { if ($count = $result->num_rows) { $rows = $result->fetch_assoc(); echo '<pre>', print_r($rows), '</pre>'; } } ?> Array ( [id] => 1 [first_name] => Bill [last_name] => John [bio] => I'm a web developer [created] => 2016-11-21 12:50:12 ) 1
  23. Lack of Error Reporting

    Hi all, <?php require 'db/connect.php'; $result = $db->query("SELECT * FROM people"); ?> I obtained a blank page running the above script, which indicates that everything is fine (path and table name are correct). I then removed the * from the query to see if it would report an error, but it did not. I added print_r($result); and run it. Nothing has changed. I included the * and run, it reported the number of rows and columns in the table. Why there has a been a lack of error reporting in this instance, noting that the ini file is configured to report all sorts of errors, even the warnings and notices (and it is reporting all that in other scripts)?
  24. Hi all, In one website, I read the following note: "You should also think about storing files locations on disk. Using MySQL for storing images is thought to be Bad Idea™. Handling SQL table with big data like images can be problematic." Do you agree? If not, why? What is the best practice in storing and displaying on a website a big number of images?
  25. 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.
×